// Many to Many in SSAS Tabular

SQL Server Tabular 2 min.

featured image Many to Many in 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. Prerequisites In order to test the new many-to-many feature from SQL Server 2016 SSAS Tabular you’ll need to download the latest CTP from Microsoft – it can be found here: Technet Also you’ll need the Visual Studio 2015 and the add-in for Business Intelligence: [...]

// Behold the New Live Query Stats in SQL Server 2016

SQL server Performance 3 min.

featured image Behold the New Live Query Stats in SQL Server 2016 With the release of SQL Server 2016 also comes a great new feature to get a live view of the current execution plan for an active query. This blogpost will cover the aspects of this new feature including: Introduction How to activate How to use and read the output Downsides – if any Introduction The introduction of live query plans are in the current release of SQL Server 2016 CTP 2. [...]

// Row Level Security in SQL Server 2016

SQL Server Security 4 min.

featured image Row Level Security in SQL Server 2016 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. A past big issue with the SQL Server engine was that in only understands tables and columns. [...]

// The DBAs Guide to Stretch Database

SQL Server DBA 5 min.

featured image The DBAs Guide to Stretch Database 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. [...]

// Enlarge AdventureWorksDW2012

SQL Server t-sql 0 min.

featured image Enlarge AdventureWorksDW2012 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. [...]

// SSIS Expressions I Often Use

SQL Server SSIS 2 min.

featured image SSIS Expressions I Often Use 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. Strings Filename from fully qualified pathstring RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),”\”,1) – 1) Folderpath from fully qualified pathstring SUBSTRING([FilePath], 1, LEN([FilePath]) – FINDSTRING(REVERSE([FilePath] ), “\” ,1 ) + 1) Foldername from fully qualified pathstring TOKEN[FilePath],”\”,TOKENCOUNT([FilePath],”\”) – 1) This is only for SSIS2012 and onwards. [...]

// Fully automate the BIML expansion

SQL Server BIML 1 min.

featured image Fully automate the BIML expansion With the MIST application from Varigence – this is possible through the command line util that ships with the installation. When the installation of MIST has finished, you’ll find a new .exe-file in the installation folder called bimlc.exe. This file is the core engine of the command line util. According to the online documentation found here – the command line util is pretty straight forward. The observant reader will notice that the link points to a documentation for hadron. [...]

// Referencing the same custom script task in SSIS with BIML

SQL Server BIML 1 min.

featured image Referencing the same custom script task in SSIS with BIML On one of my most recent projects we had a custom script task that we needed to implement in the same package several times. The code of the custom script is in its own biml-file in the project and referenced from the main biml-file. We kept getting a mysterious error when executing the package in debug mode from Visual Studio. (No warning upon building the packages). After alot of debugging and hair pulling hours, we finally got our arms around the bug. [...]

// Install SQL Server in 5 mins

SQL Server Installation 2 min.

featured image Install SQL Server in 5 mins

Install SQL Server in 5 mins

You know this everybody – you need to install a whole new SQL Server on the customers Windows Server.
And the customer doesn’t have the time or money to wait for you to click around the GUI and sit around and watch the progress-bar move every second.

So why not do this in a fully automatic way and in the same time save both effort and expensive time doing so?

[...]

// Split delimited string into rows

SQL Server t-sql 1 min.

featured image Split delimited string into rows On several occasions I’ve been assigned the task to split a delimited string into rows. I’ve done this in different ways, but never thought about the performance or stability of the different approaches for doing this. So here’s my 25 cents and findings. My first solution to this was to code a function to traverse through the string and insert a new value to a temp table for every delimiter found in the string: [...]