Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production? Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files. So I’ve come up with an alternative solution that I would like to share with you. When implemented, the process of importing flat files with changing metadata is handled in a structured, and most important, resiliant way. Even if the columns change order or existing columns are missing. Background When importing flat files to SQL server almost […]
Have you ever tried to delete an object from the database by mistake or other error? Then you should read on in this short post. I recently came across a good co-worker of mine who lost one of the views on the developer database. He called me for help. Fortunately the database was in FULL RECOVERY mode – so I could extract the object from the database log and send the script to him for his further work that day. I think I saved him a whole day of work… Here is the script I used: select convert(varchar(max),substring([RowLog Contents 0], […]
First of all, a quick recap on what a recursive query is. Recursive queries are useful when building hierarchies, traverse datasets and generate arbitrary rowsets etc. The recursive part (simply) means joining a rowset with itself an arbitrary number of times. A recursive query is defined by an anchor set (the base rowset of the recursion) and a recursive part (the operation that should be done over the previous rowset). This blogpost will cover some of the basics in recursive CTE’s and explain the approach done by the SQL Server engine.
I attended a TDWI conference in May 2016 in Chicago. Here I got a hint about the datatype hierarchyid in SQL Server which could optimize and eliminate the good old parent/child hierarchy. Until then I (and several other in the class) hadn’t heard about the hierarchyid datatype in SQL Server. So here’s an article covering some of the aspects of the datatype hierarchyid – including: Introduction How to use it How to optimize data in the table How to work with data in the hierarchy-structure Goodies Introduction The datatype hierarchyid was introduced in SQL Server 2008. It is a variable […]
Just recently I had to have a big datawarehouse solution to test some performance optimization using BIML. I could use the AdventureWorks2012 database, but I needed the clean datawarehouse tables in order to have minimum data maintennance when testing the BIML scripts. I could not find it, and figures out it was faster to make my own. So heavily inspired by this post from Jonathan Kehayias (blog), I’ve made a script that can be used to enlarge the dbo.FactInternetSales table. The script creates a new table called dbo.FactInternetSalesEnlarged and copies data from dbo.FactInternetSales into it with a randomizer. Exploding the […]
If either you are doing your SSIS by hand or using the BIML framework, you’ve came across the expressions and the expression-builder. This is a helper list, with my most often used, and wich are allways forgotten when I need them, of my commonly used SSIS expressions.
In every project on Business Intelligence there comes a time when the code needs to be deplyed to the production environment. No more development, no more manual work. But what about the partitions on the tabular cube? Do we really need to tell and learn the DBA how to handle that on a periodic plan? The answer is simple: No!
I got it! My, for now, last test was 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012. I can now call myself MSCE. The last two tests:
Ready, Steady, GO!! Today I’ve just passed the last certification on the road to MCSA: SQL Server 2012. GREAT!! What a journey and what a huge pile of books, blog-links and other stuff. The usual training kits from Microsoft Press has been read and been the base for further reading and study. A few days ago Microsoft launched their youtube channel with 3 very good videos regarding the path to MCSA: SQL Server 2012. It brings one 1 hour 15 mins long video for all three certifications. 70-461: Querying SQL Server 2012 70-462: Administering SQL Server 2012 70-463: Implementing Datawarehouse […]
Ever had that awesome SQL tracer build up that does just the right thing for your system – well I know that I had. And someday you might need just the same trace again. But now you need to build it again… Here comes the feature Extended Events in place. It was first introduced in the SQL 2008 version. The feature is a good and lightweight event-driven mechanism for collecting information about your SQL server.