Home SQL Server Archive by category "T-SQL"

Category: T-SQL

3+ part naming on Columns will be Deprecated

This is something that I’ve found a few people have missed and were surprised when I told them. This appears to have been snuck in with the Deprecated Database Engine Features in SQL Server 2016 under Features deprecated in a future version of SQL Server (note that in 2017 it is still under that heading and 2019 introduced no new deprecations). Specifically, from the documentation it states: The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been…

Continue reading

Stop using ISNUMERIC, it’s (probably) wrong

Even before SQL Server 2012, ISNUMERIC was a function that you best avoided. Put quite simply, it’s just bad. It often provides results that are wrong, and it isn’t data type specific, something that’s actually really important when you have several numerical data types to use: int/bigint and the smaller ones, numeric/decimal, float/real and money. All of these data types behave very differently when converting from a varchar. Let’s start by looking at the results for ISNUMERIC: CREATE TABLE dbo.BadNumbers(Number varchar(20)); INSERT INTO dbo.BadNumbers VALUES(‘1’), (‘7.8’), (”), (‘1,020’), (‘1,079,190.7’), (‘17,1,68,11.0’), (‘1/3’), (‘1.236E7’), (‘2d6’); SELECT Number, ISNUMERIC(Number) AS IsNumber FROM dbo.BadNumbers;…

Continue reading

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

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