T-SQL

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft. And that same thing probably happened the next several times, too.…

To find out, let’s set up a simple status log table: DROP TABLE IF EXISTS dbo.StatusLog; CREATE TABLE dbo.StatusLog (TimeItHappened DATETIME2 PRIMARY KEY CLUSTERED, Step VARCHAR(20)); GO 123456 DROP TABLE IF EXISTS dbo.StatusLog; CREATE TABLE dbo.StatusLog     (TimeItHappened DATETIME2 PRIMARY KEY CLUSTERED,     Step VARCHAR(20));GO And then let’s try a two-part transaction: BEGIN TRAN INSERT INTO dbo.StatusLog VALUES…

Aaron Bertrand posted a challenge: We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria: last placed an order more than a year ago placed…

Writing new code = bugging. That part’s easy. Taking those bugs back out, that’s the hard part. Developers are used to their tools having built-in ways to show what line of code is running now, output the current content of variables, echo back progress messages, etc. For a while, SQL Server Management Studio also had…

When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table: It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids…

SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that’s the only thing they improved – there’s still a critical performance problem with it. Let’s take the Stack Overflow database, Users table, put in an index on Location, and then test a…

SQL Server 2022 introduced a new T-SQL element, DATETRUNC, that truncates parts of dates. For example: SELECT DATETRUNC(year, '2017-06-01'); 1 SELECT DATETRUNC(year, '2017-06-01'); Truncates everything in that date other than the year, so it returns just 2017-01-01 00:00: You might ask, “Well, why not just use YEAR()?” That’s a good question – there are times…

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…

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

You’ve been working with Microsoft SQL Server for yeeears, and you’re pretty confident that you know how to write a query. So when I tell you about a book called T-SQL Fundamentals, you’re all, “nah, man, I got this covered.” You are incorrect. Sure, the first 5 chapters of the book are about 1-2 table…

In a programming language like C# or Java, you tell the computer what to do, in order. Get the customers from California, then get their invoices, then sum the total. SQL, on the other hand, is a declarative language where you declare the shape of your result set: Get the total sales from Californian customers.…

This week’s series on error handling has been an eye opener for many of us. We’ve laughed. We’ve cried. We’ve screamed in horror. We’ve read the documentation. I don’t blame you. This is a really confusing topic, and for many of us, it’s the first time we’ve actually looked at the mechanics of how transactions,…

CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY’S POST?!? I know, right? You thought that by combining try/catch with a transaction, you’d get robust error handling.  Instead, you ended up with half the tables populated, and a leftover open transaction. You’d already forgotten Tuesday’s post in which I pointed out that TRY/CATCH doesn’t catch low…

In yesterday’s epic cliffhanger, you might have been shocked to discover that a plain ol’ transaction does not solve our problem – and in fact, it makes things worse. With yesterday’s code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will…