T-SQL

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…

Say you’ve got a two-step process where you’re: Inserting rows into a table that has an identity column, then Querying that table to figure out what identities you got There’s a a faster way that doesn’t require hitting the table twice: the OUTPUT clause. I’ll demonstrate it with the Badges table from the Stack Overflow…

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…

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,…

In yesterday’s blog post about using triggers to replace computed columns, a lively debate ensued in the comments. A reader posted a trigger that I should use, and I pointed out that their trigger had a bug – and then lots of users replied in saying, “What bug?” I’ll demonstrate. We’ll take the Users table…

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.…

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(…

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…

“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;…

A perfect storm of unusual problems caused a client to ask, “What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it – like numbers or dates?” It actually works pretty well in most situations, believe it or not. The Users table of the…

If you update a row without actually changing its contents, does it still hurt? Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That’s a great post, and you should read it. But foreign keys add another level of…

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;…

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 *…

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…

Let’s get one thing out of the way first: this isn’t about the relative good or evil of triggers. I kinda like triggers, in fact – they’re good for enforcing business logic in a hurry when you can’t redo code across an entire application. Sure, in a perfect world, we’d always have the code do…

I get this question a lot, so let’s set up an example with the Stack Overflow database. My query’s goal is to: Find the top 5 Locations List the users who live in those top 5 Locations, alphabetized by their DisplayName There are a LOT of ways I could write this query, but for the…