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

Error Handling Quiz Week: Tryin’ TRY/CATCH

T-SQL
34 Comments
Let’s say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We’ll write a single stored procedure to do both inserts: DROP TABLE IF EXISTS dbo.Parent; DROP TABLE IF EXISTS dbo.Child; CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2); CREATE TABLE dbo.Child (ID INT IDENTITY(1,1),…
Read More

Most T-SQL Queries Don’t Even Try to Handle Errors.

T-SQL
46 Comments
David Tovee asked a great question in yesterday’s Mastering Query Tuning class. He asked his fellow students, “How many of you actually use TRY/CATCH?” I turned it into a Twitter poll because I wanted to check a wider audience: When the developers at my company write new queries, they use TRY/CATCH and exception handling: —…
Read More

What does SET NOCOUNT ON do?

T-SQL
13 Comments
When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers. What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation. I’ll demo it by writing a stored procedure in the Stack Overflow database to cast a vote: CREATE…
Read More
Brent Reading Book

9 Signs Your T-SQL Might Fail a Code Review

T-SQL
28 Comments
It’s hard to set absolute rules about, “Feature X should absolutely never be used.” However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As…
Read More

How to Batch Updates A Few Thousand Rows at a Time

T-SQL
17 Comments
You’ve got a staging table with millions of rows, and you want to join that over to a production table and update the contents. However, when you try to do it all in one big statement, you end up with lock escalation, large transaction log usage, slow replication to Availability Groups, and angry users with…
Read More