Welcome to 2020, may I take you coat? How about your Customer Data?

That’s right, it’s now 2020, we’re already in the 2nd decade of the “new” millennium. A lot has changed in the last 20 years: High Speed Broadband (cough for most of us) is common place, Computers are faster than ever, cars are more economical, the polar icecaps are smaller (apparently), and a Happy Meal for McDonald’s isn’t 99p any more. There are some things that haven’t change too: The UK is still part of the EU (at time of writing), your neighbour’s dog still barks all night, and customer data is still wide open to theft from SQL injection. Yes,…

Continue reading

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

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