Development

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

“Not to write any procedure over 50 lines”

Development
22 Comments
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”

Execution Plans
12 Comments
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.

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

The 201 Buckets Problem, Part 1: Why You Still Don’t Get Accurate Estimates

Statistics
7 Comments
I’ll start with the smallest Stack Overflow 2010 database and set up an index on Location: Transact-SQL USE StackOverflow2010 GO CREATE INDEX Location ON dbo.Users(Location); GO SELECT COUNT(*) FROM dbo.Users; GO 123456 USE StackOverflow2010GOCREATE INDEX Location ON dbo.Users(Location);GOSELECT COUNT(*) FROM dbo.Users;GO There are about 300,000 Users – not a lot, but enough that it will…
Read More