The ‘aggregate concatenation’ T-SQL (anti-)pattern

Some time back, Dimitri Furman who works as a Senior Consultant in the Microsoft Services team shared a very useful tip. It pertains to a pattern of usage wherein strings from multiple rows are concatenated into a single large (typically comma delimited) string. The usual way that we see this being accomplished is by code…

1

T-SQL ranking functions to the rescue once again!

This week I happened to assist with an internal application where there was a performance issue with a specific query. One of my observations with that query was that it was doing self-joins in order to determine the latest iteration of a specific record. That’s a common anti-pattern which can be fixed by using T-SQL’s…

0

Common sub-expression elimination in SQL Server: what you need to know

In working with our customers, I come across some very interesting patterns in T-SQL. Not all of these patterns are complex on the face of it, but their impact can sometimes be substantial. Here is a simple example to demonstrate what I am referring to: select CASE WHEN EXISTS (SELECT * FROM Person.Person P where…

0

Unable to launch runtime for ‘R’ script: Check this first

This post is a quickie, hoping that the tip will come in handy to others who are facing the same issue. Note: the steps below were written for SQL Server 2016 pre-release versions. They are NOT valid for RTM and subsequent releases. Today I was trying to enable the R Services (a.k.a. Advanced Analytics) feature…

1

What’s new in the Server 2016 CTP 3.3 ScriptDom Parser

We just made SQL Server 2016 CTP 3.3 publicly available a few days ago, and it has some new features which are also reflected in the managed T-SQL parser (Microsoft.SqlServer.TransactSql.ScriptDom). As readers of this blog may know, I am a big fan of this parser and to see it keeping pace with the latest preview…

2

Error 0xC1900101 – 0x20017 upgrading to Windows 10 build 10162

We are all excited about the upcoming release of Windows 10 on the 29th of July. In the meantime, you can install a preview version from the Windows Insider web page, or if you’d like to wait, you can reserve your free upgrade by following the steps here. At the beginning… At Microsoft, we are…

21

The strange case of the large LiveKernelReports folder

Some time back, I ran into a bit of a space crunch on the C: drive of my laptop which runs Windows 8.1. On digging a bit, I found a 2GB+ file at C:\Windows\LiveKernelReports\WinsockAFD-20150114-1722.dmp. Now, this was the first time I had seen a folder called LiveKernelReports and definitely the first time that I had…

1

Inline Index Definition in SQL Server 2016 CTP2

Firstly, I am sure you are as excited as I am about the next version of SQL Server, and you can try the public preview (a.k.a. Community Technology Preview 2 – CTP2) by visiting the evaluation page and install it NOW! Today morning, at the start of a  new week and I found something new…

1

SQL Server and ‘Instant File Initialization’ Under the Hood – Part 3

Welcome back! As promised last time around, here’s part 3 of the ‘Instant File Initialization’ (a.k.a. ‘IFI’) optimization for SQL Server series. If you missed the first two parts you should definitely take some time to read them first before resuming this one, because the previous posts cover a lot of things which would be…

0

SQL Server and ‘Instant File Initialization’ Under the Hood – Part 2

This is part 2 of my series on ‘Instant File Initialization’ and how that ‘brand name’ actually works under the covers. This post will take a look at what really happens when a database file is created and how the ‘Instant File Initialization’ optimization really helps from a SQL Server perspective. Before you proceed, it…

0