Fundamentals: White space and Line Breaks

Something that is important in any language is formatting. I don’t just mean in computer languages, such as Python (where good use of tabbing is required as part of the syntax), I mean in any written language. From T-SQL to English, Rust to Japanese and Braille to Russian, what ever language you’re writing it’s important to make sure it’s well presented. Even this post you’re reading now, I’ve tried to ensure has good formatting, and is why I use a code prettifier/beautifier. So, why is formatting so important? One of the biggest reasons is that it makes your code readable….

Continue reading

3 years later and STRING_SPLIT is still incomplete

Bit of a mini rant today I’m afraid. Many of us are aware that STRING_SPLIT was added in SQL Server 2016, and yet 3 years later it’s still incomplete. Why it’s incomplete There’s two major problems, in my view, with STRING_SPLIT. The first is that it doesn’t provide an ordinal position for the text extracted. For a string like ‘B,D,C,A’ this means you could just as easily get the rows A, B, C and D as you could B, D, C and A. When working with delimited data, knowing the ordinal position is often really important, and often a must….

Continue reading

Speed Test: rCTE vs Tally

Something that many of us see is the use of a recursive Common Table Expression (rCTE) to perform “iterative” tasks, something that actually can very likely be performed far quicker with a dataset approach by using a Tally Table. Not as many of us are familiar with a Tally as the rCTE, however, I’ll give a brief explanation of both, and then provide some speed tests to show by just how much a Tally can out perform an rCTE. TL;DR: Conclusion. All these tests were performed on my Home Workstation. The recursive Common Table Expression (rCTE) The name effectively gives…

Continue reading

A CURSOR free version of sp_msforeachdb

Something that we are all keenly aware of is that (generally) the use of a CURSOR in T-SQL is generally bad. A CURSOR is an iterative based method, and these generally perform very poorly in SQL Server, which is far better suited to set-based methods. sp_msforeachdb is an undocumented Special Procedure, so one day it may be removed (unannounced), but it also uses a CURSOR and can be unreliable. Something, like Aaron Bertrand did with sp_foreachdb, I have designed my own with some extra features. The Objects Let’s get to the interesting part first, the Objects. There are 2 parts…

Continue reading

Upgrading your SQL Server Version on Linux is as easy as 1,2,3

With SQL Server 2019 now released, I wanted to try upgrading on Linux as soon as I had a little free time. I was honestly surprised at how simple this really was; and how quick it was. There is documentation on all the supported OSes, but I wanted to very quickly show just how quick it was, as it really is 3 very simple steps: Remove the existing repository. I was using the CU version: sudo add-apt-repository -r ‘deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial main’ Add the new repository: sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add – sudo add-apt-repository “$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)” A…

Continue reading