How Scalar User-Defined Functions Slow Down Queries

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

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

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

“Not to write any procedure over 50 lines”

In Joe Celko’s Stairway to Database Design series, he writes: The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) This seems so radical at first, but it has precedence in NASA’s 10 Rules for Developing Safety-Critical Code: Avoid complex flow constructs, such as goto and recursion. All loops must have fixed…
Read More
Bonfire of the vanities

“UPDATE, INSERT, and DELETE are not normally processed in parallel”

Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE…
Read More

Half Of You Don’t Understand Variables and Transactions.

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