Home Archive by category "SQL Server" (Page 3)

Category: SQL Server

Fundamentals: The semicolon (;) is a statement terminator

The title pretty much says it all here, when you’re writing T-SQL, the correct place to place the semicolon (;) is at the end of all you’re statements. Something I see quite often is batches where people don’t terminate any of their statements, but do start statements that use a CTE or Merge statement with a semicolon. This means you see syntax like the below: DECLARE @MyVariable int SET @MyVariable = 5 ;WITH MyCTE AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY CONVERT(date,MyDate) ORDER BY MyDate DESC) AS RN FROM MyTable WHERE MyColumn = @MyVariable) SELECT * FROM MyCTE WHERE RN…

Continue reading

Pivoting and Unpivoting: Part 1 – An introduction

There are a couple of ways to Pivot your data in SQL Server. The first is by using PIVOT, and the other is using what is known as a cross-tab. Personally I prefer the latter as it’s more flexible and feel it’s easier to understand. I’ll explain in a later article why a cross-tab is more flexible, so I’ll be covering both methods in this article. In a addition to pivoting, you also have unpivoting. Again, you have two options here, using UNPIVOT or using a VALUES constructor. Again, I prefer the latter for the same reasons but I’ll be…

Continue reading

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