How to import flat files with a varying number of columns in SQL Server

Posted on Leave a commentPosted in custom script task, Database, Development, Script, SQL 2012, SQL 2014, SQL 2016, T-SQL

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 […]

Undelete object from database

Posted on Leave a commentPosted in Database, Development, Script, SQL 2012, SQL 2014, SQL 2016, T-SQL

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], […]

Ready, SET, go – how does SQL server handle recursive CTE’s

Posted on Leave a commentPosted in Database, Development, Performance, Script, SQL 2012, SQL 2014, SQL 2016, T-SQL

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.

Use of hierarchyid in SQL Server

Posted on Leave a commentPosted in Database, Development, Performance, Script, SQL 2012, SQL 2014, SQL 2016, T-SQL

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 […]

Update SCD Type 2 dimension in one single transaction using only T-SQL

Posted on Leave a commentPosted in Database, Development, SQL 2014, SQL 2016, T-SQL

Recently I got a request inside my organization to make sure that a dimension would keep track of the changes due to requrementes from the business. This needed to be done in a single transaction in pure T-SQL code. So – what to do and how to do it. Here’s one way. The sourcetable looks like this: The request was to keep track of changes in the ManagerId according to CaseId. I’ve created a SCD2 table like this: CREATE TABLE [dbo].[CaseProjectManagerHistory]( [dwid] [bigint] IDENTITY(1,1) NOT NULL, [CaseId] [int] NULL, [ManagerId] [int] NULL, [dwDateFrom] [date] NULL, [dwDateTo] [date] NULL, [dwIsCurrent] [bit] […]

Query store – next generation tool for every DBA

Posted on Leave a commentPosted in Ikke kategoriseret

Along with the release of SQL server 2016 CTP 3 now comes the preview of a brand new feature for on premise databases – the Query Store. This feature enables performance monitoring and troubleshooting through a log of executed queries. This blogpost will cover the aspects of this new feature including: Introduction How to activate it Configuration options What information is found in the Query Store How to use the feature What’s in it for me

Many-to-many in SSAS Tabular

Posted on 2 CommentsPosted in Ikke kategoriseret

With the release of SQL Server 2016 CTP 3.0 also comes the ability to test the Many-to-Many functionality within the SSAS Tabular. This blogpost will cover the aspects of the many-to-many feature from SQL Server 2016 – including: Prerequisites The old way The new way This post is based on data from the AdventureWorksDW2012 database.

Row level security in SQL Server 2016

Posted on Leave a commentPosted in Database, Development, Script, Security, T-SQL

With the release of SQL Server 2016 comes many great new features. One of these is the implementation of row level security in the database engine. This blogpost will cover the aspects of this new feature – including: Setup Best practice Performance Possible security leaks Introduction The row level security feature was released earlier this year to Azure – following Microsoft’s cloud-first release concept.

The DBAs guide to stretch database

Posted on Leave a commentPosted in Database, Development, SQL 2016

One of the new features in SQL Server 2016 – and there is a lot – is the ability to stretch the on premise databases to an Azure environment. This blogpost will cover some of the aspects of this – including: Primarily setup – how to get started Monitoring state of databases that are in ‘stretch mode’ Daily work with stretch databases Backup – what’s good to know With the release of SQL Server 2016, the new feature called stretch database is also released.