Development

3 Ways to Debug T-SQL Code

T-SQL
37 Comments
Writing new code = bugging. That part’s easy. Taking those bugs back out, that’s the hard part. Developers are used to their tools having built-in ways to show what line of code is running now, output the current content of variables, echo back progress messages, etc. For a while, SQL Server Management Studio also had…
Read More

How to Find Missing Rows in a Table

T-SQL
21 Comments
When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table: It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids…
Read More

Should You Use SQL Server 2022’s STRING_SPLIT?

T-SQL
2 Comments
SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that’s the only thing they improved – there’s still a critical performance problem with it. Let’s take the Stack Overflow database, Users table, put in an index on Location, and then test a…
Read More

Should You Use SQL Server 2022’s DATETRUNC?

T-SQL
10 Comments
SQL Server 2022 introduced a new T-SQL element, DATETRUNC, that truncates parts of dates. For example: SELECT DATETRUNC(year, '2017-06-01'); 1 SELECT DATETRUNC(year, '2017-06-01'); Truncates everything in that date other than the year, so it returns just 2017-01-01 00:00: You might ask, “Well, why not just use YEAR()?” That’s a good question – there are times…
Read More

Slow “Having” Query? Try Pre-Filtering.

T-SQL
11 Comments
I was helping a client with a query, and I’m going to rework the example to use the Stack Overflow database for easier storytelling. Say we need to: Find all the locations where users have logged in since a certain date, then Return the total count of people who live in those locations One way…
Read More

#tsql2sday: Start Your Dynamic SQL with a Comment.

T-SQL
6 Comments
When you write dynamic SQL, start like this: CREATE OR ALTER PROC dbo.MyProc AS BEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT /* MyProc */ '; ... 12345 CREATE OR ALTER PROC dbo.MyProc ASBEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT  /* MyProc */ '; ... Right after the SELECT (or INSERT or UPDATE or whatever),…
Read More
And it looks bad even in shades.

PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing

Parameter sniffing is a notorious problem for Microsoft SQL Server because it tries to reuse execution plans, which doesn’t work out well for widely varying parameters. Here’s a primer for the basics about how it happens. SQL Server 2022 introduces a new feature called Parameter Sensitive Plan optimization. I’m not really sure why Microsoft capitalized…
Read More

SQL Server 2022 Tells You Why A Query Can’t Go Parallel.

Until 2022, when a query couldn’t go parallel, all we got was a really cryptic note in the execution plan properties saying NonParallelPlanReason = CouldNotGenerateValidParallelPlan. But starting with SQL Server 2022, even when I’m running under older compatibility levels: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */ GO DECLARE @TableVariable TABLE(Total BIGINT); INSERT…
Read More

“Index Seek” Doesn’t Mean Much.

Execution Plans
13 Comments
When you see “index seek” on an execution plan, that doesn’t mean SQL Server is jumping to exactly the row you’re looking for. It only means that SQL Server is seeking on the first column of the index. This is especially misleading on indexes where the first column isn’t very selective. To explain, I’ll take…
Read More

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it. It might be overzealous index rebuilds. Let’s demo why by starting a new query in the Stack…
Read More