SQL Server 2016 is Out. Here's the Fine Print.

It's July 14, 2026. SQL Server 2016 just reached end of extended support. Ten years of service, and as of today: no more security patches, no more bug fixes, no more calling Microsoft when it's on fire. We've been telling you this day was coming since February. It's not coming anymore. It's here.

And here's the irony. Nothing will break today. Your 2016 instances will hum along like nothing happened. No error, no warning, no countdown clock in SSMS. The engine doesn't know it has been abandoned. That silence is exactly what will get people -- the risk doesn't announce itself, it just accumulates. Unless you're paying for ESUs, every CVE from here on out ignores your 2016 boxes completely.

Two Things Happened Today, Not One

Today is also Patch Tuesday. And this particular Patch Tuesday flips the Kerberos RC4 hardening (CVE-2026-20833) into its final enforcement phase. We covered exactly what that does to SQL Server logins last month: legacy service accounts still leaning on RC4 stop getting Kerberos tickets, and what you see is the world's least helpful error:

Cannot generate SSPI context.

Think about it. Which estates are most likely to have decade-old service accounts with legacy encryption types? Exactly. The estates still running SQL Server 2016. If connections start failing this week, don't immediately assume it's the EOL. That EOL breaks nothing today. Check the domain controllers' patch status first, then work through the RC4 remediation steps in my June 18 post.

Six Things 2016 Users May Not Know

1. Azure is no longer a free pass.

This is the big one. For SQL Server 2014, the play was simple: lift the VM into Azure and the Extended Security Updates came free. Everyone learned that trick. Everyone is now assuming it still works. It doesn't. Under Microsoft's pricing consistency model that took effect April 1, 2026, SQL Server 2016 ESUs are chargeable everywhere -- on-prem, other clouds, Azure VMs, even Azure Stack. Same list price regardless of where it runs or how you buy. If your Azure migration budget assumed free ESUs -- the way it worked for 2014 -- that line item just became a real cost. Redo the math before you sign anything.

2. Procrastinating won't save you a dime.

Thinking you'll skate unpatched until a scary CVE drops, then buy ESUs? Microsoft thought of that. If you subscribe late, your first bill includes a one-time bill-back charge all the way to the start of the ESU term. Sign up in December, pay from July. The meter started at midnight whether you're enrolled or not.

3. Your support plan just went quiet.

Per Microsoft's own ESU FAQ, you cannot log a support ticket for SQL Server 2016 anymore -- even if you have a paid support plan. No ESU subscription, no ticket. Plenty of shops budget for a support contract as their safety net and have no idea the net was removed this morning.

4. Express, Web, and Developer can't buy their way out.

ESUs are available for Enterprise and Standard editions only. If you have 2016 Express instances squirreled away under desks and inside vendor appliances -- and you do -- there is no ESU option for them at any price. Upgrade or retire. End of story.

5. Windows Server 2016 did NOT die today.

This one has been widely misreported. The two products do not share a deathbed. SQL Server 2016 ended today, July 14, 2026, but Windows Server 2016 runs until January 12, 2027. If a box runs both, that's one application stack with two lifecycle deadlines, six months apart. The database is the urgent conversation, the OS is the January conversation. Plan them as related projects, not one generic '2016 upgrade'.

6. ESU patches are not Patch Tuesday.

Even if you pay, don't expect a monthly cadence. ESUs cover critical security updates only, released when a qualifying vulnerability requires one. No bug fixes, no non-critical patches, no features, no design changes. It's a security drip-line, not a servicing plan. Oh -- and even Volume Licensing purchases require Azure Arc registration to activate. There is no ESU path that avoids Arc entirely.

Find Your Exposure -- Right Now

Run this on anything you suspect. It works on SQL Server 2008 and later, so it's safe on the old stuff, ie., the boxes we're actually worried about. If ProductVersion starts with 13., congratulations, you own an unsupported database server as of this morning:

/* what am I actually running? (safe on SQL 2008+) */
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,   -- 13.x = SQL Server 2016
       SERVERPROPERTY('ProductLevel') AS ProductLevel,       -- RTM / SPn
       SERVERPROPERTY('Edition') AS Edition,
       @@VERSION AS FullVersionString;                       -- last line reveals the host OS

Check FullVersionString, too. If the tail end says 'Windows Server 2016', that box has BOTH deadlines from item #5 -- database now, OS in January.

Then check whether each 2016 instance is even allowed to buy the lifeboat:

/* ESU eligibility check -- Enterprise and Standard only */
SELECT @@SERVERNAME AS ServerName,
       SERVERPROPERTY('Edition') AS Edition,
       CASE
           WHEN CAST(SERVERPROPERTY('Edition') AS VARCHAR(64)) LIKE 'Enterprise%'
             OR CAST(SERVERPROPERTY('Edition') AS VARCHAR(64)) LIKE 'Standard%'
           THEN 'ESU eligible'
           ELSE 'NOT ESU eligible -- upgrade or retire' END AS ESU_Status;

Sweep your whole estate, not just the servers you remember. Registered Servers or a CMS query against every instance takes minutes. The 2016 box that hurts you won't be the one on your inventory sheet -- it'll be the 'temporary' one from 2018 that a vendor installed and nobody documented.

The Dates That Matter Now

Date	What Happens
July 14, 2026	SQL Server 2016 extended support ends. Today.
July 15, 2026	ESU Year 1 billing begins (midnight UTC), enrolled or not.
January 12, 2027	Windows Server 2016 extended support ends. Separate deadline.
July 17, 2029	SQL Server 2016 ESU availability ends. The real cliff.

So What Do You Do This Week?

Inventory first -- every instance, every edition, every host OS -- using the scripts above. Then triage each instance into one of three buckets:

Bucket	What Goes In It
Upgrade now	Customer-facing or compliance-scoped. PCI-DSS, HIPAA, SOC 2 auditors flag unsupported software.
Bridge with ESUs	Vendor-locked or politically complicated. Can't move yet, can't leave exposed.
Retire	That report server nobody has touched since 2021. You can now justify the decom.

If you're upgrading, target SQL Server 2022 or 2025 -- don't burn a migration on 2017, which itself dies in October 2027. And read what breaks on the Monday after a 'successful' upgrade before you schedule the weekend. The engine is the easy part. The drivers, certificates, and linked servers standing around it are not.

SQL Server 2016 was a genuinely great release -- Query Store, Always Encrypted, temporal tables, the version that made 'just put it in Standard Edition' a real option. It earned the decade. But the calendar has no snooze button, and staying on 2016 now means accepting risk that compounds daily -- unpatched, and by choice. If you need help planning your exit, let me know. This is what we do. Better to plan it now than triage it later. 😉

More to Read

Microsoft Learn: SQL Server Extended Security Updates FAQ
MSFT Tech Community: SQL Server 2016 Extended Security Updates
Microsoft Licensing: ESU Pricing Consistency Update
sqlfingers: Cannot Generate SSPI Context: The July RC4 Change That Breaks SQL Logins
sqlfingers: Your SQL Server 2016 Upgrade Will Succeed. Then Monday Happens.
sqlfingers: SQL Server 2016: 111 Days. The Last Patch Just Dropped.

The Apostrophe That Wasn't There — Until SQL Server Created It

A few weeks back, Redgate's Simple Talk pulled apart a SQL injection hole in one of Microsoft's own shipped system procedures, sys.sp_dbmmonitorupdate. That is the part that should stop you. Microsoft wrote it. This procedure is not careless. It is sanitized. But it doubled up its single quotes with REPLACE, exactly the way the textbook says to, and became a target anyway.

Last time I wrote about injection, the lesson was 'stop concatenating user input.' This is the sequel for anyone still concatenating and trying to make it safe with REPLACE. The problem is that the value can contain a Unicode character that looks exactly like an apostrophe, but isn't one. REPLACE looks only for the real apostrophe and lets the lookalike pass. Then the value lands in a char buffer, and SQL Server quietly converts the lookalike into a genuine apostrophe — without being asked.

Input:   Oʼreilly     (that mark is a lookalike, not a real apostrophe)

Step 1   REPLACE only doubles REAL apostrophes. It does not recognize
         the lookalike, so it changes nothing and passes it through.

Step 2   The value is copied into a char (non-Unicode) buffer. That
         copy silently converts the lookalike into a REAL apostrophe.

Result   A real, unescaped apostrophe is now sitting in the string you
         already 'sanitized'. The quote breaks out. Injection is live.

This has been labeled 'Unicode homoglyph' since Redgate's Simple Talk showed a real system-procedure case. In SQL Server, this means a character that visually resembles another character -- like lowercase 'L' and the number 1, or as in the example above, a Unicode apostrophe and a standard single quote. These homoglyphs become dangerous when an implicit Unicode-to-non-Unicode conversion performs a best-fit character mapping. In this case, SQL Server silently translated U+02BC into U+0027 after the quote-doubling had already occurred, effectively creating a new SQL delimiter that the sanitization step never saw.

Why doubling quotes stops working

First, one piece of vocabulary, because the rest of this leans on it. Every character has an ID number, its Unicode code point, written as 'U+' plus a hex value. The apostrophe on your keyboard is U+0027. It has a near-twin, the modifier letter apostrophe (U+02BC), a completely different character that happens to look identical in most fonts.

Now the failure. REPLACE matches on that ID number. You told it to find the keyboard apostrophe (U+0027) and double it, so when you hand it the twin (U+02BC) there is no match and it passes straight through. The damage lands one line later, when that value is assigned to a non-Unicode variable: SQL Server's best-fit mapping rewrites the twin into the real keyboard apostrophe. A genuine quote is now sitting in a string you already declared clean.

You can see it here. This part is deterministic, not a measurement:

DECLARE @n nvarchar(10) = N'ʼ';   -- U+02BC, MODIFIER LETTER APOSTROPHE
DECLARE @v varchar(10)  = N'ʼ';   -- implicit cast to non-Unicode

SELECT UNICODE(@n) AS AsNVarchar,   -- 700
       UNICODE(@v) AS AsVarchar;    -- 39  (that is U+0027, a real quote)

700 goes in, 39 comes out — and notice there is no CAST or CONVERT anywhere in that code. Just assigning the Unicode value (the N'...') to a varchar variable is enough. SQL Server converts it for you, silently, using best-fit character mapping. The implicit conversion is the whole bug.

demo time

IF OBJECT_ID('dbo.usp_FindCustomer','P') IS NOT NULL 
DROP PROCEDURE dbo.usp_FindCustomer;
GO
IF OBJECT_ID('dbo.Customer','U') IS NOT NULL 
DROP TABLE dbo.Customer;
GO

CREATE TABLE dbo.Customer
(
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(128) NOT NULL
);
GO

-- Three rows, including a REAL apostrophe surname. Row count hard-capped by the VALUES list.
INSERT dbo.Customer (LastName) 
VALUES ('Smith'), ('O''Brien'), ('Nguyen');
GO

The 'safe' procedure

It sanitizes. It doubles the quotes like the textbook says. Its only sin is landing the result in a char(256) buffer before building the command.

CREATE PROCEDURE dbo.usp_FindCustomer
    @Name NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- The step everyone trusts: double up single quotes.
    SET @Name = REPLACE(@Name, '''', '''''');

    -- The mistake: a NON-Unicode buffer. This is where U+02BC becomes U+0027.
    DECLARE @command CHAR(256);
    SET @command = N'SELECT TOP (100) CustomerId, LastName '
                 + N'FROM dbo.Customer WHERE LastName = ''' + @Name + N'''';

    PRINT @command;      -- inspect the built string instead of blindly running it
    -- EXEC (@command);  -- left commented ON PURPOSE
END;
GO

Case 1 — a normal apostrophe (this one is fine)

EXEC dbo.usp_FindCustomer @Name = N'O''Brien';

The REPLACE in the proc turned that one apostrophe into two, so the command it builds ends with the double ticks in the LastName. That is not a typo. In T-SQL, using '' inside a string is the escape for a single apostrophe. When SQL Server runs that, it reads the value back as O'Brien. Copy that SELECT and run it yourself. You'll see it is working correctly -- and that is what lulls you into trusting REPLACE.

Case 2 — the lookalike (this one is not)

-- The character before 'reilly' is U+02BC, NOT the U+0027 on your keyboard.
EXEC dbo.usp_FindCustomer @Name = N'Oʼreilly';

This time REPLACE finds no U+0027 to double, so it does nothing. The char(256) assignment converts U+02BC into a real apostrophe, and the output now carries a quote that cannot be used. Try to run that SELECT and you'll see the engine doesn't care for that unmatched quote:

You're only seeing this error because this payload was clumsy enough to break the syntax. Make the injected tail valid T-SQL and nothing breaks. SQL Server just runs it, no error, no warning, with whatever rights the caller holds. The unclosed quote syntax error is the best outcome on the table. A real attacker's version will run clean and provide no evidence of who is in your system doing what.

The fix: keep it Unicode, and stop concatenating

Two things, belt and suspenders. First, never let sanitized values fall into a non-Unicode buffer. The implicit conversion is what creates the vulnerability. Microsoft's fix for the reported system-procedure case was simple: they widened the command buffer to nvarchar(4000) so the U+02BC never gets a chance to collapse into a quote. Second, and better, do not concatenate the value into the SQL string at all. Parameterize it, so the value is data and never code.

CREATE OR ALTER PROCEDURE dbo.usp_FindCustomer_Fixed
    @Name NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Stays Unicode end to end: no best-fit collapse, no minted apostrophe.
    DECLARE @command NVARCHAR(4000);
    SET @command = N'SELECT TOP (100) CustomerId, LastName '
                 + N'FROM dbo.Customer WHERE LastName = @Name';

    -- @Name crosses as a PARAMETER, never concatenated into the text.
    EXEC sys.sp_executesql
         @command,
         N'@Name NVARCHAR(128)',
         @Name = @Name;
END;
GO

Now run both inputs with the fixed proc and there is no drama. The @Name value is not parsed as SQL. It is bound as a parameter, compared as data, and they both run properly. O'Brien returns and the lookalike matches no one and returns nothing. No conversion or broken quote. The empty result is the whole win. No REPLACE required, and that is the point. Hand-rolled escaping is a maintenance trap; parameters are the actual answer.

Cleanup

IF OBJECT_ID('dbo.usp_FindCustomer','P') IS NOT NULL DROP PROCEDURE dbo.usp_FindCustomer;
IF OBJECT_ID('dbo.usp_FindCustomer_Fixed','P') IS NOT NULL DROP PROCEDURE dbo.usp_FindCustomer_Fixed;
IF OBJECT_ID('dbo.Customer','U') IS NOT NULL DROP TABLE dbo.Customer;

Version notes

Nothing here needs a modern build. sp_executesql has shipped since forever, and the conversion behavior applies to any supported SQL Server version. One honest caveat -- best-fit mapping is collation dependent, so which Unicode lookalikes collapse into which ASCII characters can vary by the collation in play. The defense, however, does not vary. Keep the buffer Unicode and pass values as parameters, and the mapping never gets a turn.

More to Read

Simple Talk: Exposing a SQL injection vulnerability you have never heard of
Remus Rusanu: SQL Injection — casting can introduce additional single quotes
sqlfingers inc: AI Didn't Hack Mexico. SQL Injection Did

#tsql2sday #200: I Bet It's Bad If I See a Column Wrapped in a Function in the WHERE Clause

This post is part of T-SQL Tuesday #200, hosted this month by Brent Ozar. The prompt: "When I'm looking at a query, I bet it's bad if I see ____."

Easy. I didn't even have to think about it. When I open a stored procedure and see a function wrapped around a column in the WHERE clause, I groan. Out loud. Because more often than not, it means the predicate is non-SARGable, and non-SARGable means your indexes just became very expensive shelf decorations.

The Groan

WHERE UPPER(LastName) = 'SMITH'
WHERE ISNULL(Status, '') = 'Active'
WHERE CONVERT(varchar(10), OrderDate, 120) = '2026-07-01'
WHERE YEAR(OrderDate) = 2026
WHERE LTRIM(RTRIM(AccountCode)) = 'A100'

Every one of these looks reasonable. Defensive, even. Handle the NULLs. Normalize the case. Trim the whitespace. The query returns the correct rows, runs fine against 10,000 rows in development, survives code review, and then quietly falls apart against a five million row table in production.

Here's the problem: SARGable is short for 'Search Argument Ability', which means a predicate the engine can use to seek into an index. The very moment you wrap the column within a function in your WHERE clause, SQL Server can no longer navigate the index using the stored key values. It has to compute the function's output for every single row first, and then compare. Your index seek instantly becomes an expensive index or table scan, and nothing in the results tells you it happened.

That's why this is my number one groan over the flashier disasters. A 400-line temp table pileup announces itself. This one hides. It's the difference between a query that's obviously bad and a query that looks ok but is lying to you.

Prove It with A Demo

Let's build a table with a million rows and an index that should make this query trivial.

USE YOURDBNAME;
CREATE TABLE dbo.OrdersDemo (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    OrderStatus VARCHAR(20) NULL,
    OrderDate DATETIME2(0) NOT NULL,
    AcctCode VARCHAR(10) NOT NULL
);

-- load 1 million rows
INSERT INTO dbo.OrdersDemo (OrderStatus, OrderDate, AcctCode)
SELECT TOP (1000000)
    CASE ABS(CHECKSUM(NEWID())) % 4 
         WHEN 0 THEN 'Active' 
         WHEN 1 THEN 'Closed' 
         WHEN 2 THEN 'Pending' 
         ELSE NULL END,
    DATEADD(MINUTE, -1 * (ABS(CHECKSUM(NEWID())) % 525600), '2026-07-01'),
    'A' + RIGHT('000' + CAST(ABS(CHECKSUM(NEWID())) % 500 AS varchar(3)), 3)
FROM sys.all_columns a
CROSS JOIN sys.all_columns b;

CREATE NONCLUSTERED INDEX idx_OrdersDemo_OrderStatus 
    ON dbo.OrdersDemo (OrderStatus);

Now the "careful" version, with statistics on:

SET STATISTICS IO ON;
SELECT COUNT(*) 
FROM dbo.OrdersDemo
WHERE ISNULL(OrderStatus, '') = 'Active';

Run that and look at the Messages tab. The number that matters is logical reads, which is how many pages SQL Server had to touch to answer the query. (Ignore 'Scan count'. That's just how many times the object was accessed, and it says 1 in both of our tests.) Wrapping OrderStatus in ISNULL forced SQL Server to read every page and compute the function a million times, with these costs:

Now stop and think about what ISNULL is actually contributing here. Remember, a NULL Status was never going to equal 'Active' anyway. The wrapper isn't protecting anything. It's pure cost. Remove it:

SELECT COUNT(*) 
FROM dbo.OrdersDemo
WHERE OrderStatus = 'Active';

Same results. 630 logical reads instead of 1,982 - a third of the I/O, and that's with 'Active' being a full quarter of the table. The only real difference between the two queries is the function around the column in the WHERE clause. The more rare your target rows, the wider this gap gets. On this toy table it's milliseconds, but when it's the 40M-row order table with twelve other predicates stacked up, this becomes the difference between a report that runs in 2 seconds and one that runs much longer and makes your users call you for help.

The Rewrites

Almost every function-wrapped column in a WHERE clause has a SARGable rewrite. Move your work to the other side of the comparison - the literal or the variable - or restate the logic so the column remains bare on the left.

The Optimizer is incredibly good at using indexes. The trick is that you need to give it a predicate it can actually reason with.

The Groan	The Rewrite
UPPER(LastName) = 'SMITH'	LastName = 'Smith' (assuming CI collation)
ISNULL(Status,'') = 'Active'	Status = 'Active'
CONVERT(varchar(10), OrderDate, 120) = '2026-07-01'	OrderDate >= '2026-07-01' AND OrderDate < '2026-07-02'
YEAR(OrderDate) = 2026	OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
LTRIM(RTRIM(AcctCode)) = 'A100'	Fix the data on the way IN, not on every read

Two notes on that table. First, UPPER() on a case-insensitive collation (the default for most of us) is doing literally nothing except killing your seek. Check your collation before you use that. Second, notice that every fix above is just a query rewrite. No schema changes, no new objects, just restating the predicate so the column on the left remains bare. More often than not, the SARGable version already exists. You don't need to redesign anything. You just need to remember that wrapping the column with a function in your WHERE clause is NOT the approach you want to take - when it can be avoided.

Why This Is Getting Worse, Not Better

In the T-SQL Tuesday #200 invitation, Brent jokes that the offending query was probably written by that one person on your team, and that they were probably using AI. He's right. AI-generated T-SQL loves wrapping WHERE clause columns in ISNULL and UPPER and TRIM. AI often produces code that looks perfectly fine, which is exactly what makes it dangerous. It compiles and returns correct rows - it just scans everything. The model optimizes for looking right, not for the logical reads. If your shop is merging AI-assisted database code, non-SARGable predicates should be at the top of your review checklist - right next to invented column names.

The Cleanup

DROP TABLE IF EXISTS dbo.OrdersDemo;

Honorable mention groan: 300 lines of temp table DDL stitched together with multiple UNIONs. That one's getting its own post later. Consider it a preview of the next installment of the Cursed SQL series.

More to Read

T-SQL Tuesday #200 invitation - Brent Ozar
Why SQL Server Is Ignoring Your Index
Clustered and nonclustered indexes - Microsoft Learn

Birthday Brain Teasers

Tomorrow is my birthday! 🥳 I will be 80 feet underwater, where Teams cannot reach me and nothing deadlocks.

But before I go under, something fun. Four puzzles. They all look trivial, but they also all return results you weren't expecting. Junior DBA or seasoned MVP, I'd like to see your answers in the comments before you go into SSMS to run the queries -- that will make this fun.

Challenge 1: The Two Functions That Are Not the Same

Every report writer reaches for ISNULL and COALESCE to swap a blank for something friendlier. Most of us treat them as the same thing. Here they are, side by side, on the same value:

DECLARE @DiveNote VARCHAR(4) = NULL;

SELECT ISNULL(@DiveNote,   'Cancelled') AS Using_ISNULL,
       COALESCE(@DiveNote, 'Cancelled') AS Using_COALESCE;

Before you run it: both columns say 'Cancelled', right? Commit to an answer, then run it and see.

1. Explain why one column does not say what you expected.
2. Name one more way ISNULL and COALESCE quietly disagree.

Hint: one of these two functions cares very much about the first value it is handed.

Challenge 2: The Filter That Returns Everything

The sites I am hoping to dive are in #Dives, and the one I've already enjoyed is in #Logged:

-- sites I want to check out
IF OBJECT_ID('tempdb..#Dives') IS NOT NULL DROP TABLE #Dives;
CREATE TABLE #Dives (DiveSite VARCHAR(50));
INSERT INTO #Dives VALUES ('Yucab'), ('Santa Rosa Wall'), ('Columbia');

-- sites already logged 
IF OBJECT_ID('tempdb..#Logged') IS NOT NULL DROP TABLE #Logged;
CREATE TABLE #Logged (SiteName VARCHAR(50));
INSERT INTO #Logged VALUES ('Palancar Caves');

The question is simple. Which of my hoping-to dives have I already logged? 🤿

Before you run it: how many rows will come back, and which ones? Commit to an answer, then run it and see.

SELECT DiveSite
FROM #Dives
WHERE DiveSite IN (SELECT DiveSite FROM #Logged);

1. Explain why a filter for a single logged site behaves the way it does.
2. Change one thing so SQL Server throws a loud error here instead of quietly handing you the wrong answer.

Hint: look very closely at the column names in the two tables. SQL Server never did.

Challenge 3: The Running Total That Lies

Here is my dive log with a running total of Joy Points, ordered by date. Two dives on July 2, one on July 3:

IF OBJECT_ID('tempdb..#DiveLog') IS NOT NULL DROP TABLE #DiveLog;
CREATE TABLE #DiveLog (
    DiveDate DATE,
    Site VARCHAR(50),
    JoyPoints INT
);

INSERT INTO #DiveLog VALUES
('2026-07-02', 'Dalila', 100),
('2026-07-02', 'el Cedral', 100),
('2026-07-03', 'Palancar Horseshoe', 50);

-- running total
SELECT DiveDate, Site, JoyPoints,
       SUM(JoyPoints) OVER (ORDER BY DiveDate) AS RunningJoy
FROM #DiveLog;

A running total, so the obvious answer for RunningJoy is 100, then 200, then 250. Right?

Run it. Notice the first two rows show the same number, and the running total is already ahead of itself on dive one. Then tell me:

1. Why does the total leap forward before the second dive has happened?
2. What single clause makes it count the way a running total actually should?

Hint: the default window frame is not the one you think you asked for.

Challenge 4: Pin My Dive to the Top

No trick this time -- just a clever technique for ordering when you need to do something funky. It happens. Just read. You'll get what I mean.

IF OBJECT_ID('tempdb..#Wishlist') IS NOT NULL DROP TABLE #Wishlist;
CREATE TABLE #Wishlist (DiveSite VARCHAR(50));
INSERT INTO #Wishlist VALUES 
('Santa Rosa Wall'), ('Palancar Caves'), ('Columbia'), ('Yucab');

I want 'Palancar Caves', the site I have already logged, pinned to the very top of the list when I query my dives. Everything else should fall underneath alphabetically. One SELECT, no UNION.

Write the ORDER BY that does it in the comments -- and if you use something other than a CASE, I want to see it.

Hint: you are allowed more than one sort key, and the first one does not have to be a column.

Drop Your Answers Below! 👇

No peeking into SSMS until you've committed to your answers. Post your answers for each challenge in the comments, and tell me why.

This past year has been a big one for me at sqlfingers and I thank you all for being part of my journey. Cheers to another trip around the sun... ideally without too many deadlocks. 🥂

Cursed SQL, Part Three: Six Numbers That Lie

Two rounds of Cursed SQL so far. First came six queries that run fine until they don't, then six ways NULL lies to your face. Here is round three, and this time the villain is arithmetic.

Every example below either quietly returns the wrong answer or explodes because the answer won't fit. SQL Server did exactly what its rules say. Same curse as always, but the danger is not always an error. Sometimes it is a wrong answer delivered with a straight face. Paste each into a query window and watch.

Curse 1: Integer Division Floors Your Percentage to Zero

You want the percent complete. Completed over total, times one hundred. It could not be simpler.

DECLARE @Completed int = 30, @Total int = 100;
SELECT @Completed / @Total * 100 AS PctComplete;  -- 0

Thirty out of a hundred. You expect 30 but you get 0.

huh?   Both operands are integers, so SQL Server does integer division: 30 / 100 is not 0.3, it is 0, because the fractional part gets thrown away before anything else happens. End result, 0 * 100 = 0. The multiplier never had a chance.

The fix is to force one operand to a decimal before the division, so the division itself is done in decimal math:

SELECT @Completed * 100.0 / @Total AS PctComplete;   -- 30.000000

The order matters here. @Completed / @Total * 100.0 still gives you 0.0, because the integer division happens first and hands a 0 to the multiply. You need to promote the type early -- multiply by 100.0 up front, or cast a column before you divide it. The behavior follows straight from the division operator's type rules: int over int yields int.

Run them yourself, you'll see.

Curse 2: @@IDENTITY Hands You the Trigger's Key, Not Yours

This one doesn't just return a wrong number, it writes one into your data. You insert an order, grab the new identity, and use it as a foreign key value. Standard stuff. Except there is an audit trigger on the table that nobody told you about.

CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    Amount MONEY NOT NULL
);

CREATE TABLE dbo.OrderAudit
(
    AuditID INT IDENTITY(1000,1) PRIMARY KEY,   -- starts at 1000 on purpose
    OrderID INT NULL,
    LoggedAt DATETIME DEFAULT SYSDATETIME()
);
GO

CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders AFTER INSERT
AS
BEGIN
    INSERT dbo.OrderAudit (OrderID) SELECT OrderID FROM inserted;
END
GO

INSERT dbo.Orders (Amount) VALUES (49.99);
SELECT @@IDENTITY AS Identity_Returned,    -- the AUDIT row
       SCOPE_IDENTITY() AS Scope_Returned; -- the ORDER row

Your new OrderID is 1. But look what the two functions hand back:

@@IDENTITY returns 1000 as the AuditID of the row the trigger inserted because @@IDENTITY reports the last identity generated anywhere in your session, regardless of scope. The trigger fired inside your session, inserted last, and won. Write that into your FK value and you've just pointed a child row at an order that does not exist. Use SCOPE_IDENTITY() to stay inside your scope and return the value that you intended, like this:

SELECT * FROM dbo.Orders; -- see what's in the table

DECLARE @NewOrderID int;
INSERT dbo.Orders (Amount) VALUES (49.99);
SET @NewOrderID = SCOPE_IDENTITY();   -- correct for this single-row insert
SELECT @NewOrderID AS NewOrderID;     -- 2  (the order you just inserted)

SELECT * FROM dbo.Orders; -- see what's in the table

DBA Bonus

SCOPE_IDENTITY() is the correct fix here because this example inserts a single row. But if you're writing new code that inserts multiple rows, the OUTPUT clause is an even better pattern because it captures every identity value generated by the statement.

DECLARE @NewOrders TABLE
(
    OrderID int
);

INSERT dbo.Orders (Amount)
OUTPUT INSERTED.OrderID
INTO @NewOrders (OrderID)
VALUES (49.99);

SELECT *
FROM @NewOrders;

Curse 3: Money in FLOAT Drifts

You stored amounts in FLOAT because it was there and it held decimals. Then the report got flagged a penny short and you couldn't find that penny anywhere. Here it is:

SELECT CASE WHEN CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) = CAST(0.3 AS FLOAT)
            THEN 'equal' ELSE 'NOT equal' END AS FloatCheck;  -- NOT equal

NOT equal? Technically, one tenth plus two tenths is not three tenths -- as you can see here:

SELECT CAST(CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) AS DECIMAL(19,17)) AS FloatSum;

FLOAT is an approximate numeric type. Values like 0.1 and 0.2 have no exact representation in binary, so their sum lands a hair off of 0.3. Harmless when you are measuring distances to a star, but fatal when you're dealing with finance. Run that across real orders and those hairs will build into real cents. A SUM of FLOAT amounts does not match the same data summed as DECIMAL. Financial data belongs in DECIMAL, where values are represented exactly rather than approximately.

SELECT CASE WHEN CAST(0.1 AS DECIMAL(4,2)) + CAST(0.2 AS DECIMAL(4,2)) = CAST(0.3 AS DECIMAL(4,2))
     THEN 'equal' ELSE 'NOT equal' END AS DecimalCheck;   -- equal

Curse 4: CONVERT to INT Truncates -- and the Money Disappears

Same value, same CONVERT -- but the data type you use may either round or just lose cash. Convert an amount to INT and SQL Server truncates it; convert that very same amount to DECIMAL and it rounds. Watch one line item of $2.99:

SELECT CONVERT(INT, 2.99) AS AsINT,             -- 2  the 0.99 is chopped off
       CONVERT(DECIMAL(19,0), 2.99) AS AsDEC,   -- 3  rounded to the nearest dollar
       CONVERT(DECIMAL(19,2), 2.99) AS AsMoney; -- finance won't be coming after you now

Look at those top two. One value, two answers, and neither is $2.99. Converting to INT drops everything after the decimal point. It does not round. It just chops, so the ninety-nine cents is just gone. Converting to a zero-scale DECIMAL rounds, which is a bit tidier, but it still throws the cents away. Just in the other direction.

The safer method is to store financial values in a DECIMAL with the necessary precision and scale, so every penny survives and every calculation returns accurately. Avoid converting money to whole-number types unless losing the cents is truly the point.

Curse 5: SUM Overflows the Column It Started In

A total that runs clean for years -- right up until the numbers get bigger. SUM over an INT column returns an INT, and the moment the running total crosses INT's ceiling, we've got a problem.

SELECT SUM(Amount) AS Total
FROM (VALUES (CAST(2000000000 AS INT)),
             (CAST(2000000000 AS INT))) AS t(Amount);

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Each value fits into an INT fine, but their sum, four billion, does not -- it exceeds INT's ceiling of 2,147,483,647, and the SUM overflows rather than widening to hold the result. CAST to BIGINT before you sum, and the accumulator has room:

SELECT SUM(CAST(Amount AS bigint)) AS Total
FROM (VALUES (CAST(2000000000 AS int)),
             (CAST(2000000000 AS int))) AS t(Amount);   -- 4000000000 

Curse 6: DATEDIFF Overflows at Scale

This is the merciful curse that actually throws an error instead of silently lying to you. Measuring the seconds between two timestamps separated by years is real work: high-frequency analytics, scientific modeling, precise duration tracking all depend on it. The expression looks harmless and runs fine for years, until someone hands it a wide enough range:

SELECT DATEDIFF(SECOND, '2000-01-01', '2100-01-01') AS SecondsApart;

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating
two date/time instances is too large. Try to use datediff with a less precise datepart.

DATEDIFF returns an INT. A century in seconds is about 3.15 billion -- well past INT's ceiling of 2,147,483,647 -- so it overflows. It runs clean across small ranges and detonates the day a report spans too many days. Intentionally or by fat finger, it's going to happen. The fix is DATEDIFF_BIG, which returns a BIGINT (SQL Server 2016 and later), or a coarser datepart if you do not need second-level precision:

SELECT DATEDIFF_BIG(SECOND, '2000-01-01', '2100-01-01') AS SecondsApart; -- 3155760000

In Summary

Six shapes, one lesson: SQL Server does the math you tell it to, but not always the math that you actually meant. Integer division throws away the fraction before you ever multiply. @@IDENTITY answers for the whole session, not just your statement. FLOAT only approximates. Converting money to int chops the cents instead of rounding them. SUM keeps its total until it overflows, and DATEDIFF quietly runs on an INT until the range is too large -- and it overflows as well.

None of these are bugs. They're all consequences of SQL Server's rules, and every one has a small, deliberate fix. Promote the type before you divide, reach for SCOPE_IDENTITY(), store money in DECIMAL(p,s) for financial calculations, and use DATEDIFF_BIG when the span is wide. Spot the shape and the curse lifts. Now you have six more to watch for.

Cleanup

DROP TABLE dbo.Orders;        -- the audit trigger drops with it
DROP TABLE dbo.OrderAudit;

More to Read

Cursed SQL: Six Queries That Run Fine Until They Don't
Cursed SQL, Part Two: Six Ways NULL Lies to Your Face
Microsoft Learn: Divide (/) (Transact-SQL)
Microsoft Learn: SCOPE_IDENTITY (Transact-SQL)
Microsoft Learn: float and real (Transact-SQL)
Microsoft Learn: CAST and CONVERT (Transact-SQL)
Microsoft Learn: DATEDIFF_BIG (Transact-SQL)

SQL Server 2025 ZSTD LEVEL Isn't Exactly What You Think It Is

SQL Server 2025's new ZSTD backup compression is a real upgrade. It's also one of the more over-hyped ones. Ask the internet. You'll hear it shrinks your files 30-50%, halves your RTO, and even powers row and page compression. One of those is half-true, one is shaky, and one is just wrong. Here's the version backed by people who actually ran the backups.

First, the good news: there's nothing to 'turn on'. No configuration setting to enable and no restart. ZSTD is just a value you pass to the COMPRESSION option in your backup command like this:

BACKUP DATABASE YourDB
TO DISK = 'C:\MSSQL\Backup\YourDB.bak'
WITH COMPRESSION (ALGORITHM = ZSTD);   -- defaults to LEVEL = LOW

That one word -- ZSTD -- is the whole feature. But what does it do? How is it any different from the compression you were already using?

Three compression algos

That question has three possible answers, because SQL Server 2025 gives you three backup compression algorithms to choose from. The one real, unarguable win for ZSTD over the 2022 addition is that it needs no special hardware.

Algorithm	Since	Special hardware?
MS_XPRESS	2008	No (still the default)
Intel QAT	2022	Yes -- Intel QAT
ZSTD	2025	No

But picking ZSTD is only half the decision. Unlike MS_XPRESS, ZSTD lets you dial the effort with a LEVEL setting, and that's where the real choice lives. There are three, and if you don't name one you get LOW:

... WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = LOW);      -- the default
... WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = MEDIUM);
... WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH);

The instinct might be to read LOW, MEDIUM, HIGH as 'a little', 'some', and 'a lot' of compression, and then reach for HIGH -- but that's wrong. You have to know what the three level settings actually do to understand why.

Think of the LEVEL setting as more of a speed dial than a size dial.

Here's what those three levels really influence. Moving from LOW to HIGH always trades more CPU and backup time for additional compression, but on many real-world databases the extra space savings are surprisingly small. Think of the LEVEL setting as more of a speed dial than a size dial, and reaching for HIGH because it sounds better may not have the impact you're looking for.

Run the demo below against a real database and you'll watch it happen. On my ~2 GB test, ZSTD LOW finished about 38% faster than MS_XPRESS (roughly 3.0 versus 4.8 seconds) while producing a bak file almost exactly the same size. LOW already matches the old default and beats it on the clock. And here's the kicker: LOW actually finished faster than backing up with no-compression at all, because compression left so much less to write to disk.

In my test, MEDIUM and HIGH shaved well under 1% off the size, and HIGH took about 3x as long as LOW to do it. Triple the work to shave a rounding error. My maintenance windows are already too tight to spend that without a better gain.

The speed win travels. On a far larger ~389 GB real-world set, Anthony Nocentino clocked ZSTD LOW about 44% faster than MS_XPRESS -- but on his data the LOW file came out slightly larger than MS_XPRESS, where on mine it came out a touch smaller. That's the real takeaway. LOW's most consistent win is the clock, not the byte count. Which way the size tips depends on your data, so don't bank on LOW to beat MS_XPRESS on size. Bank on it to beat MS_XPRESS on time -- that part holds.

Level	Speed vs MS_XPRESS	Size vs MS_XPRESS	Reach for it when
LOW (default)	Faster	About the same	Almost always -- the sweet spot
MEDIUM	Slower	A hair smaller	You want every last MB and can spare the time
HIGH	Much slower	Barely smaller than MEDIUM	Archival / offsite, time truly doesn't matter

Prove it with your own numbers

Compression ratios and timings swing with your data, so the only numbers that matter are yours. Here's a self-contained demo that builds a ~2 GB lab database, backs it up five ways while timing each to the millisecond, and prints a scoreboard. Point it at a real dev database for even cleaner gaps. ZSTDdemo and C:\MSSQL\Backup\ are placeholders -- rename yours to suit.

1. Build and inflate a lab database

The trick to realistic numbers is mixing data that compresses with data that doesn't. Like a repetitive text column that squashes down nicely, plus a random binary column that won't budge -- that's the type of blend you'd see in real pages. This demo builds about 2 GB. Plan for ~5 GB of .bak files across the five runs, so be sure you've got enough space free on your backup drive.

USE master;
GO
CREATE DATABASE ZSTDdemo;
GO

ALTER DATABASE ZSTDdemo SET RECOVERY SIMPLE;
ALTER DATABASE ZSTDdemo MODIFY FILE (NAME = 'ZSTDdemo', SIZE = 3GB, FILEGROWTH = 512MB);
GO

USE ZSTDdemo;
GO

-- heap (no clustered key) so the bulk load can be minimally logged
CREATE TABLE dbo.Filler
(
    ID INT IDENTITY(1,1) NOT NULL,
    Padding CHAR(2000) NOT NULL,      -- repetitive -> compresses
    Noise VARBINARY(2000) NOT NULL    -- random     -> will not compress
);
GO

SET NOCOUNT ON;
DECLARE @batches INT = 100, @i INT = 0;   -- ~20 MB/batch; 100 ~ 2 GB. Bump for more.
WHILE @i < @batches
BEGIN
    INSERT dbo.Filler WITH (TABLOCK) (Padding, Noise)
    SELECT TOP (5000)
           REPLICATE(CONVERT(CHAR(50),
               CONCAT('cust-', a.object_id, '-region-', a.object_id % 7, '-')), 40),
           CRYPT_GEN_RANDOM(2000)
    FROM sys.all_objects a CROSS JOIN sys.all_objects b;

    SET @i += 1;
    IF @i % 20 = 0 CHECKPOINT;            -- keep the log small
END;
GO

2. Back it up five ways, timed, and read the scoreboard

Backup history only records start/finish to the second, which rounds sub-second backups to zero. So the demo times itself to the millisecond into a temp table and then joins to history for the sizes. Run this whole block in one window -- the temp table only lives for this session.

USE master;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t (Test sysname, StartedAt datetime2(3), EndedAt datetime2(3));
DECLARE @s datetime2(3);

SET @s = SYSDATETIME();
BACKUP DATABASE ZSTDdemo TO DISK = 'C:\MSSQL\Backup\ZSTDdemo_none.bak' 
  WITH INIT, FORMAT, NO_COMPRESSION, NAME = 'NONE';
INSERT #t VALUES ('NONE', @s, SYSDATETIME());

SET @s = SYSDATETIME();
BACKUP DATABASE ZSTDdemo TO DISK = 'C:\MSSQL\Backup\ZSTDdemo_xpress.bak'
  WITH INIT, FORMAT, COMPRESSION (ALGORITHM = MS_XPRESS), NAME = 'MS_XPRESS';
INSERT #t VALUES ('MS_XPRESS', @s, SYSDATETIME());

SET @s = SYSDATETIME();
BACKUP DATABASE ZSTDdemo TO DISK = 'C:\MSSQL\Backup\ZSTDdemo_zstd_low.bak'
  WITH INIT, FORMAT, COMPRESSION (ALGORITHM = ZSTD, LEVEL = LOW), NAME = 'ZSTD LOW';
INSERT #t VALUES ('ZSTD LOW', @s, SYSDATETIME());

SET @s = SYSDATETIME();
BACKUP DATABASE ZSTDdemo TO DISK = 'C:\MSSQL\Backup\ZSTDdemo_zstd_med.bak'
  WITH INIT, FORMAT, COMPRESSION (ALGORITHM = ZSTD, LEVEL = MEDIUM), NAME = 'ZSTD MEDIUM';
INSERT #t VALUES ('ZSTD MEDIUM', @s, SYSDATETIME());

SET @s = SYSDATETIME();
BACKUP DATABASE ZSTDdemo TO DISK = 'C:\MSSQL\Backup\ZSTDdemo_zstd_high.bak'
  WITH INIT, FORMAT, COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH), NAME = 'ZSTD HIGH';
INSERT #t VALUES ('ZSTD HIGH', @s, SYSDATETIME());

-- scoreboard: timing from the harness, sizes from backup history
SELECT r.Test,
       DATEDIFF(MILLISECOND, r.StartedAt, r.EndedAt) Millis,
       CAST(bs.backup_size / 1048576.0 AS DECIMAL(10,1)) RawMB,
       CAST(bs.compressed_backup_size / 1048576.0 AS DECIMAL(10,1)) FileMB,
       CAST(bs.backup_size * 1.0
            / NULLIF(bs.compressed_backup_size, 0) AS DECIMAL(5,2)) Ratio
FROM #t AS r
CROSS APPLY (
    SELECT TOP (1) backup_size, compressed_backup_size
    FROM msdb.dbo.backupset
    WHERE database_name = 'ZSTDdemo' 
    AND name = r.Test
    ORDER BY backup_finish_date DESC
) AS bs
ORDER BY r.StartedAt;

IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t;

My scoreboard, on a ~2 GB database:

Read it like this. NONE is your uncompressed baseline, and MS_XPRESS is the old default you're trying to beat. Compare every ZSTD row against the MS_XPRESS row. FileMB answers 'is it smaller?' -- not by much -- and Millis answers 'is it faster?'. LOW and MEDIUM, yes, but what happened to HIGH? We have significantly increased duration for almost no shrinkage at all.

3. Confirm what actually landed in the file

Don't take it on faith -- RESTORE HEADERONLY reads the header without restoring, and the CompressionAlgorithm column is your receipt. Run this and be sure it says 'ZSTD'.

RESTORE HEADERONLY
FROM DISK = 'C:\MSSQL\Backup\ZSTDdemo_zstd_low.bak';

The fine print they skip

30-50% is a ceiling, not a promise. That figure is Microsoft's internal benchmark. Plenty of independent testers never hit it. Remember, this depends entirely on your data shape. Treat it as best-case, not as expected.

Encrypted data barely compresses -- with any algorithm. If you see a claim that ZSTD can find hidden patterns in column-encrypted data or TDE, ignore it. Encrypted bytes are high-entropy by design, and Microsoft's own docs are clear that compressing encrypted backups may not shrink them much at all. You should always compress before you encrypt anyway. If you compress after encryption, the storage savings will be negligible with too much overhead.

Faster restores are real, but secondary. ZSTD does decompress efficiently, and Nocentino measured restore improvements -- so it's a genuine perk. Just don't expect it to halve your RTO on its own. Restore time is dominated by I/O, not decompression.

This is backup compression, full stop. ZSTD in SQL Server 2025 applies to BACKUP. It does not change ROW or PAGE data compression, regardless of what you may read elsewhere.

Version requirements

ZSTD backup compression requires SQL Server 2025 (17.x) or later, any edition, Windows or Linux, no special hardware. It's T-SQL only -- there's no checkbox for it in SSMS and you don't run sp_configure. It's just in your BACKUP command like the examples above. Also important, Ola Hallengren's backup solution already supports ZSTD, so you're good there, if you're running that like so many of the rest of us. Thank you, Ola!

If you only remember one thing from this post, it should be that you should benchmark your own data. ZSTD's LEVEL changes both backup time and compression ratio, but on many real-world databases, the difference in backup time is far more than the difference in backup size.

More to Read

Backup Compression (SQL Server) -- Microsoft Learn
BACKUP (Transact-SQL) -- Microsoft Learn
Anthony Nocentino -- Using ZSTD Backup Compression
Simple Talk -- SQL Server 2025 ZSTD Backup Compression Guide

CU6 Is Here. SESSION_CONTEXT Is Still Broken in Parallel

Back in March I wrote about a SESSION_CONTEXT bug that lies to you in parallel plans, with wrong results or AV dumps, no error, no warning. At the time it had survived since January 2022, all the way through SQL Server 2019, 2022, and 2025 CU2, and I just flagged it because it was still not fixed.

SQL Server 2025 CU6 dropped on June 17th (build 17.0.4055.5, KB5093421), and...

It's Still There

CU6 ships 19 fixes on top of CU5. Plenty got cleaned up. But scroll down to Known issues in this update and there it is again, same as before:

Queries that use the built-in SESSION_CONTEXT function might return incorrect results 
or trigger access violation (AV) dump files when run in parallel query plans. This issue 
occurs because of the manner in which SESSION_CONTEXT interacts with parallel execution 
threads, particularly if the session is reset for reuse.

Three years, three major versions, and now six CUs deep in the SQL Server 2025 release cycle. That darned bug refuses to die. If your app calls sp_set_session_context to stash a TenantID and your queries filter on it, all it takes is the right parallel plan under the affected conditions to potentially serve up the wrong tenant's rows — no error, no warning, just incorrect results.

The 30-Second Check

Confirm where you actually are. CU6 reports build 17.0.4055.5:

SELECT
    SERVERPROPERTY('ProductVersion') AS Build,   -- 17.0.4055.5 = CU6
    SERVERPROPERTY('ProductUpdateLevel') AS CU;  -- CU6

The Workaround Still Works

Same fix as last time, because the bug is unchanged. Trace flag 11042 forces any query referencing SESSION_CONTEXT to run serially, which sidesteps the parallel-thread propagation problem entirely.

-- Global, takes effect immediately
DBCC TRACEON (11042, -1);

-- Or make it permanent as a startup parameter: -T11042

It is a tradeoff -- you give up parallelism on those specific queries -- so test it before you push to prod. The full why-and-how is in the March post and nothing about the mechanics has changed.

An Old Friend Two Rows Down

SESSION_CONTEXT is not the only familiar face in the CU6 known issues. The MSDASQL linked-server error 7416 I covered earlier this month is still parked on the list too -- it has been riding along since CU4. If a linked server goes quiet after you patch, the fix could be just a 'User ID' away.

Patch for the 19 fixes. Just know that one of the bugs you might be patching for is still sitting there, politely waiting, exactly where we left it in March.

More to Read

SESSION_CONTEXT: Three Years, Two Bugs, One Workaround
Cumulative Update 6 for SQL Server 2025 (KB5093421)
SESSION_CONTEXT (Transact-SQL) - Known Issues

SQL Server Isn't Dying. It's Being Promoted to Cash Cow

Last week, The Register ran a line about SQL Server that I haven't been able to shake: that it's "too lucrative to ditch, but too legacy to love". Both halves are true, and they pull in opposite directions. One half is why you can sleep at night. The other is why you should be paying attention.

I've built a career on this engine, so 'too legacy to love' lands a little sharp. But the market gets a vote, too, so let's do this with evidence, not feelings.

The Stage SQL Server Didn't Get

The loudest signal this year wasn't an announcement. It was a matter of placement. At Build 2026, the keynote spotlight went to AI agents, and the headline database announcement was HorizonDB. SQL Server had its sessions, but it didn't have the stage.

That placement is louder than it looks, because SQL Server 2025 had only just shipped, carrying native vector search that one analyst rated its strongest addition in a decade. There were breakout sessions on SQL Server and Azure SQL AI features, so it wasn't absent from the event. But the keynote spotlight and the headline database announcement went elsewhere, and for a flagship-grade release that's the tell.

Two personnel facts sit underneath that. Rohan Kumar, the long-time SQL Server champion inside Microsoft, left the company in June. The SQL Server remit now belongs to Arun Ulag, president of Azure Data, who also runs Fabric and a portfolio of open-source database services. The product no longer has its own champion at the top; it shares one with the very platform that's replacing it in the spotlight.

And then there's the licensing. Microsoft's own terms let you carry SQL Server licenses over to AWS's RDS service without paying twice. Read that again. They are fine with you running SQL Server on a direct competitor's cloud, so long as the license revenue keeps landing in Redmond.

Why It's Not Going Anywhere

Now the other half. SQL Server is not dying as a product, and anyone telling you otherwise is selling a migration.

Cloud and AI get most of the public attention, but on-prem systems are still the bedrock of enterprise operations: the things that record a sale or clear a bank transaction. Microsoft pulls roughly $15 billion a year from the on-prem DBMS market, most of it SQL Server, and that business was still growing around 8% in 2025. Of the major legacy database vendors, Microsoft is the only one to have grown its DBMS market share over the last fifteen years, reaching roughly 33% of the on-prem market and trailing only Oracle. This is a winning business, not a dying one.

You're not getting orphaned either. Support for SQL Server 2025 runs through 2036. That's a decade-plus of patches and updates on a version you can install today.

Where the Energy Actually Went

So if the money is flowing and support runs to 2036, what's the problem? The problem isn't survival. It's direction.

In the cloud, PostgreSQL has become the de facto API for relational databases. It's gone so far that vendors now ship Postgres-compatible wire protocols on purpose, as an insurance policy against vendor lock-in. Microsoft isn't fighting that current. They're swimming in it, and their answer is HorizonDB -- a cloud-native, PostgreSQL-compatible database of its own. It runs on a custom, memory-safe Rust storage engine and scales to 128 TB and thousands of vCores. It ships DiskANN vector search and mirrors directly into Fabric, and Microsoft is now the number-one PostgreSQL committer among the hyperscalers.

At the same time, the operational databases like Cosmos, Azure SQL, and Postgres, are being pulled under the Fabric umbrella, and Microsoft has been clear that Fabric is where it expects its growth to come from. That is the strategy. Period.

Developer mindshare tells the same story from the ground up. The DB-Engines popularity race at the top is tightening:

Engine	Rank	Score
SQL Server	#3	698
PostgreSQL	#4	688

Those DB-Engines numbers are from June 2026. Oracle and MySQL still lead the overall table, so the real story is that ten-point gap between #3 and #4, and the direction it's been closing. Developer mindshare points the same way. In the 2025 Stack Overflow survey, Postgres is now the most-used database among professional developers at 55.6%, up from 48.7% a year earlier, while SQL Server sits fourth. The engine that gets championed is not the one the next generation reaches for first.

Cash Cow, Not Flagship

Put those three things side by side and a shape appears. A cash cow is a product a company milks for steady, low-effort revenue while it spends its real innovation budget somewhere else. That's not an insult. It's a portfolio decision, and a common one.

Watch how cleanly SQL Server fits the pattern. Steady milking: $15 billion a year, license portability even onto a rival's cloud, support locked in to 2036. Growth budget elsewhere: HorizonDB, Fabric, the Postgres committer push. The soft signals: the champion gone, the remit folded into the Fabric executive's portfolio, a fresh release that drew sessions but not the stage. None of those is a death warrant.

Each is a product that earns its keep while the attention moves elsewhere. The open-source tide is running toward Postgres, but SQL Server's stability, security, and deep ecosystem keep it entrenched in corporate stacks that aren't going anywhere -- which is what makes it a good cow to be milking. The revenue and the licensing economics tilt the same way: toward Azure, where Fabric and HorizonDB are waiting. SQL Server doesn't have to win for Microsoft to win, as long as your workload ends up in their cloud, feeding the newer, bigger tools.

That's the read I'd stand behind: SQL Server isn't dying. It's being quietly promoted to cash cow. Maintained, not championed.

To be clear, that's my read of the evidence, not an official press release. Microsoft will tell you SQL Server 2025 is a major release, and on the technical merits, they're right. But where a company aims its keynote, its org chart, and its committer headcount tells you where it thinks the future lives, and right now, none of those arrows point at SQL Server.

What This Means If You Run It

So what do you actually do with this? First: don't panic. The engine is excellent, funded, and supported for years. Don't let a blog post talk you into a migration you don't need.

Second: know exactly where you stand. Run this and write down the answer.

-- Where am I, and how long is it supported?
SELECT 
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel')   AS product_level,
    SERVERPROPERTY('Edition')        AS edition;

Map that version to its support horizon and plan your upgrades on your timeline.

Third: read the room. Microsoft is pointing its own future in two directions, and neither one is SQL Server. One is Postgres, the engine. The other is Fabric, the platform it wants your data to live in. Both are worth your time. Pick up Postgres so you're bilingual at the engine level, and get familiar with Fabric so you're not a stranger to where the data's expected to land. Not because SQL Server is going away (it isn't), but because the people signing the checks have told you which direction they're headed. That's just good career insurance.

Too lucrative to ditch, too legacy to love. I'll quibble with the second half. I've loved worse things for worse reasons, and this engine has earned its keep many times over. But I can't argue with the evidence behind it. SQL Server is in no danger. It's just no longer the thing Microsoft is excited about. Plan accordingly.

More to Read

The Register – Too lucrative to ditch, too legacy to love
Stack Overflow 2025 Developer Survey – Databases
DB-Engines – SQL Server popularity trend
Microsoft – Azure HorizonDB
Related: SSIS Isn't Dead Either. Yet.

Copilot in SSMS Runs As You — Until You Fence It In

Here is a piece that many people miss when they wire up GitHub Copilot in SSMS: when Copilot runs a query, it runs as you. Same login, same permissions, and same blast radius. Connect as sysadmin and your helpful AI assistant is also sysadmin — db_owner in means db_owner out.

That is fine when you are poking at a dev box, but it's a different thing entirely when a DBA with db_owner in production asks Copilot to 'clean up some old rows', and Copilot happily obliges without any safety checks. SSMS 22.7 finally gives us a way to fence that in.

What changed in 22.7

Starting in SSMS 22.7, you can give Copilot its own execution context — a dedicated identity that is separate from the user driving it. You point Copilot at a database user or SQL login, and from then on every query Copilot runs executes as that principal instead of the human at the keyboard. The DBA keeps their own access in the Query Editor and Object Explorer, but what Copilot executes on their behalf is now restricted to only what Copilot's identity can access. See Microsoft's writeup, Introducing execution context for GitHub Copilot in SSMS.

I believe this is a remarkable step forward in the AI realm that we now exist in.

The setting is called agentExecuteAsUser, and it lives in the frontmatter of that database's CONSTITUTION.md, which is the markdown instruction block SSMS stores as a database-level extended property. It is configured per database. When Copilot connects, it reads the constitution, and if it finds agentExecuteAsUser, Copilot then executes database actions under that user context.

Set it up

Here is a self-contained walk-through with two tables: one Copilot is allowed to touch and one that it isn't.

USE DBA;
GO

CREATE TABLE dbo.Orders (OrderID int IDENTITY PRIMARY KEY, Customer nvarchar(50), Total money);
CREATE TABLE dbo.Payroll (EmpID int IDENTITY PRIMARY KEY, EmpName  nvarchar(50), Salary money);
GO

INSERT dbo.Orders (Customer, Total) VALUES (N'Acme', 100.00), (N'Globex', 250.00);
INSERT dbo.Payroll (EmpName, Salary) VALUES (N'Employee A', 90000), (N'Employee B', 120000);
GO

Now we create the identity Copilot will run as, and grant it only the permission it needs -- SELECT on Orders, nothing else. No rights on Payroll at all.

USE DBA;
GO

-- The least-privilege identity Copilot executes as
CREATE USER CopilotExec WITHOUT LOGIN;
GO

-- Only what Copilot needs. SELECT on one table, full stop.
GRANT SELECT ON dbo.Orders TO CopilotExec;
GO

Now store the CONSTITUTION.md as the database's extended property. The agentExecuteAsUser line goes in the frontmatter: the block at the very top of the CONSTITUTION.md file, opened and closed by its own '---' line, as the code below shows. Everything after that closing '---' is the body, where the behavioral guidelines go.

USE DBA;
GO

DECLARE @DBUser NVARCHAR(128) = N'CopilotExec';

DECLARE @constitution NVARCHAR(4000) = N'---' + CHAR(10)
    + N'agentExecuteAsUser: ' + @DBUser + CHAR(10)
    + N'---' + CHAR(10)
    + N'# Database Constitution' + CHAR(10)
    + CHAR(10)
    + N'## Coding Guidelines' + CHAR(10)
    + N'- Never suggest DROP statements' + CHAR(10)
    + N'- Always include a WHERE clause on UPDATE or DELETE' + CHAR(10)
    + N'- Avoid SELECT * in production code';

IF EXISTS (SELECT 1 FROM sys.extended_properties
           WHERE class = 0 AND name = N'CONSTITUTION.md')
    EXEC sp_updateextendedproperty @name = N'CONSTITUTION.md', @value = @constitution;
ELSE
    EXEC sp_addextendedproperty @name = N'CONSTITUTION.md', @value = @constitution;
GO

One last step that is easy to forget. The human using Copilot must be able to impersonate CopilotExec, or the engine will not let Copilot switch context. Grant IMPERSONATE to your Copilot user — here, the database user is me.

USE DBA;
GO

GRANT IMPERSONATE ON USER::CopilotExec TO [sqlfingers\rlewis];
GO

First, let's verify the SQL Server boundary manually. This simulates the execution context SSMS Copilot should use when agentExecuteAsUser is active.

USE DBA;
GO

EXECUTE AS USER = 'CopilotExec';
GO

SELECT
    USER_NAME() AS DatabaseUser,
    ORIGINAL_LOGIN() AS OriginalLogin,
    HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'SELECT') AS CanSelectOrders,
    HAS_PERMS_BY_NAME('dbo.Payroll', 'OBJECT', 'SELECT') AS CanSelectPayroll;

SELECT * FROM dbo.Orders;    -- returns rows
SELECT * FROM dbo.Payroll;   -- Msg 229, denied
GO

REVERT;
GO

We see results from the top two SELECTs but that call to Payroll fails with Msg 229. This proves the database wall we just put up works. CopilotExec can read Orders but the call to Payroll fails with Msg 229.

Now that we've proven the SQL Server boundary manually, let's verify the permissions available to the execution context itself.

EXECUTE AS USER = 'CopilotExec';
GO

SELECT
    USER_NAME() AS DatabaseUser,
    HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'SELECT') AS CanSelectOrders,
    HAS_PERMS_BY_NAME('dbo.Payroll', 'OBJECT', 'SELECT') AS CanSelectPayroll;
    
REVERT;
GO

As intended, CopilotExec can read Orders but not Payroll. With that established, I then asked Copilot to return the top 10 rows from dbo.Payroll:

There it is. Copilot can only query what CopilotExec is privileged for. It sees that it doesn't have SELECT on Payroll and very kindly gives us a script to do it ourselves.

Even better, Copilot did not simply obey the prompt. It recognized the execution context defined in the database constitution and determined that the current context did not have permission to access the requested table. To me, THAT is the type of governance we need in AI to manage data safely, legally, and ethically.

Two boundaries, and only one of them is real

It is worth being precise about what is actually protecting you here, because the constitution does two different jobs:

Mechanism	Type	Enforced by
agentExecuteAsUser	Hard boundary	SQL Server permissions on the impersonated user
Constitution body (ie., 'never DROP')	Soft boundary	The model choosing to behave

The guidelines in the body steer behavior. They do not stop anything. Microsoft is blunt about this in the Agent mode docs: "The security boundary is SQL Server's permission enforcement, not Copilot's approval system." The approval prompts and the READ_ONLY default are seatbelts. The permissions on agentExecuteAsUser are the wall. You need to build the wall AND enforce seatbelt usage.

Gotchas worth knowing before you ship it

Gotcha	What happens
Missing IMPERSONATE grant	Copilot cannot assume the configured agentExecuteAsUser identity, so the execution context never activates
Per database	It is not server-wide. Set this on every database you care about
Lazy account choice	Do not point it at sa, dbo, or a broad admin login because it is convenient. That defeats the feature

Version requirements

Requires SSMS 22.7 or later with the AI Assistance workload installed (it is a separate component in the Visual Studio Installer), plus a GitHub account with Copilot access. SSMS 22.7.0 shipped June 9, 2026 and 22.7.1 followed on June 16 with a query-editor fix for large scripts. 22.7.1 is the most current SSMS 22 build.

Again, I think this is a real step forward for better controls with AI and SQL Server, but it still requires a common-sensical approach with best practices. Follow the least privilege principle with your agentExecuteAsUser just like you would any other user. Grant the minimum permissions needed for the tasks at hand. Just like Erin Stellato said, "The tighter the permissions on this user, the more confidence you have in what GitHub Copilot in SSMS can and can't do." Read more on the Copilot best practices here.

More to Read

Introducing execution context for GitHub Copilot in SSMS
Announcing the Release of SSMS 22.7.0 - and many previews
GitHub Copilot Agent Mode (Preview) - Microsoft Learn
Database Instructions (CONSTITUTION.md / AGENTS.md) - Microsoft Learn
Best Practices for GitHub Copilot in SSMS - Microsoft Learn