Home Posts tagged "SQL Server"

Tag: SQL Server

3 years later and STRING_SPLIT is still incomplete

It’s finally (more or less) complete! Well, it’s now 2022, and we can finally say that STRING_SPLIT is "complete"; or at least better (not accepting multi-character delimiters is still a problem). In Azure SQL Database, and in SQL Server 2022, STRING_SPLIT now has an optional bit parameter, enable_ordinal, which if 1 is passed to will mean that an additional column called ordinal is returned, which (unsurprisingly) returns the ordinal position of the value in the delimited list. By default, the value is 0 for the parameter, so if you did have any production code with SELECT * (though I hope…

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

Why I define my Carriage Returns and Line Breaks as variables in Dynamic SQL

A question I’ve been asked on several occasions is “Why use NCHAR(13) + NCHAR(10), or @CRLF in your code, and not use inline new lines in your Dynamic SQL?” So, what someone means is why do I write my dynamic statements like this: DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10); SET @SQL = N’SELECT *’ + @CRLF + N’FROM ‘ + QUOTENAME(@TableName) + N’;’; Instead of code like this: DECLARE @SQL nvarchar(MAX); SET @SQL = N’SELECT * FROM ‘ + QUOTENAME(@TableName) + N’;’; I actually have a couple of reasons for this. SQL Server is on Windows and…

Continue reading