T-SQL

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
21 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

Using Triggers to Replace Scalar UDFs on Computed Columns

T-SQL
46 Comments
Your database is riddled with computed columns whose definition includes a scalar user-defined function. Even up to & including SQL Server 2019, which boasts faster scalar function processing, any table that includes a scalar function cause all access to that table to go single-threaded. In that case, a trigger can actually be a great replacement.…
Read More

How to Create a Table with a Partitioned Clustered Columnstore Index

T-SQL
8 Comments
If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here’s a creation T-SQL example: CREATE TABLE dbo.MyTableName( Id int IDENTITY(1,1), SalesDate datetime NOT NULL, INDEX MyIndexName CLUSTERED COLUMNSTORE ) ON ps_MyPartitionFunction(SalesDate); GO 123456 CREATE TABLE dbo.MyTableName(…
Read More

Half Of You Don’t Understand Variables and Transactions.

T-SQL
32 Comments
Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens? DECLARE @MySalary INT = 100000; BEGIN TRAN SET @MySalary = @MySalary * 2; ROLLBACK; SELECT @MySalary; 1234567 DECLARE @MySalary INT = 100000; BEGIN TRANSET @MySalary = @MySalary * 2;ROLLBACK; SELECT @MySalary; I…
Read More

Your Views Aren’t The Problem. Your Code Is.

T-SQL
37 Comments
“I hate views,” the DBA said. “They kill performance. And nested views are even worse.” Wrong. I’ll prove it. I’m going to use the 50GB StackOverflow2013 database, and I’ll start by creating a couple of indexes on the Users table to help our queries, then create a view on the Users table: Transact-SQL USE StackOverflow2013;…
Read More

Free Webcast on Dynamic SQL Pro Tips

Dynamic SQL is one of the most powerful tools in the database developer’s arsenal. When you need a complex search stored procedure that takes all kinds of parameters (price, category, location, color), dynamic SQL can run extremely quickly by leveraging better indexes. However, when done wrong, it’s extremely painful to troubleshoot. I’ve been working with…
Read More

How to Pass a List of Values Into a Stored Procedure

T-SQL
61 Comments
Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here’s what the table looks like: And here’s what my starting stored procedure looks like: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsersByLocation @SearchLocation NVARCHAR(40) AS SELECT * FROM dbo.Users WHERE Location = @SearchLocation ORDER BY DisplayName;…
Read More

How to Remove Times from Dates in SQL Server

T-SQL
34 Comments
Say that you’ve got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table: And let’s say you wanna find the users who last accessed the site on September 9, 2018 – regardless of the time. Here are a few ways to do it: Transact-SQL SELECT *…
Read More

Cool Query Hints

T-SQL
12 Comments
The SQL Server documentation has a pretty cool list of query hints: Yeah, I surf in dark mode. I’m a dark kinda guy. But wait – what’s that colored box? ENHANCE! I could make jokes here, but … every single thing in that caution is true. SQL Server really does typically select the best execution…
Read More