Slow “Having” Query? Try Pre-Filtering.

T-SQL
2 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

How Scalar User-Defined Functions Slow Down Queries

T-SQL
8 Comments
When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it’s doing in your execution plan. To show it, I’ll run a simple query against the Users table in the Stack Overflow database. SELECT TOP 100 DisplayName, Location, Reputation, Id FROM dbo.Users ORDER…
Read More

Why Full Text’s CONTAINS Queries Are So Slow

T-SQL
17 Comments
SQL Server’s full text search is amazing. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings,…
Read More