sqlfingers.com

Friday, July 17, 2026

The SQL Server DBA's Guide to AI Tools in 2026

Two years ago, 'AI for SQL Server' mostly meant pasting your T-SQL into a web browser, wrestling with prompts until it stopped hallucinating and screaming at ChatGPT. Today, the market has matured and split into three distinct battlefields: writing T-SQL faster, agentic DBA operations (plan tuning, audits, diagnostics), and building text-to-SQL solutions. The right tool depends on what you're trying to accomplish, because AI for SQL Server has become a collection of specialized tools rather than one-size-fits-all assistants.

Before comparing tools, it's worth acknowledging that much of the published 'AI tool comparison' content is written by vendors ranking themselves first, and several widely quoted benchmarks weren't even run against SQL Server. Rather than marketing claims, this post leans primarily on practitioner experience from SQL Server DBAs, in addition to the vendor documentation.

The Good

GitHub Copilot completions in SSMS -- the easy win

Copilot is now a standard SSMS 22 workload: sign in with a GitHub account and you get inline T-SQL completions (my fave), chat, and database-aware answers grounded in your active connection. The free tier (about 50 requests per month) costs nothing to try, and Pro is $10/month. As of SSMS 22.3, you can even store coding-standard instructions as extended properties at the database or object level, and Copilot uses them when generating code, which is a genuinely useful way to make its output match your shop's standards.

Here's the important part: Copilot in SSMS is really two features wearing one name, and they've earned very different reputations. The inline completions are excellent. Brent Ozar recommends enabling them immediately and letting Copilot handle repetitive joins, syntax, and boilerplate. The chat and Agent features are a very different story. See 'The Ugly' below.

Before you standardize on it, keep two things in mind. First, the free tier disappears quickly once you use Copilot for more than code completion. Second, your prompts and database metadata are sent to GitHub-hosted models, which may be a show-stopper if your environment prohibits sending schema or query text outside your organization.

dbForge AI Assistant -- best add-in for pure query development

Devart's AI Assistant plugs into dbForge Studio for SQL Server or into standard SSMS via dbForge SQL Complete. It's great at converting natural language to valid T-SQL, explaining legacy code, troubleshooting syntax errors, and offers optimization suggestions -- all without leaving SSMS or dbForge Studio.

It has genuine schema awareness, deep IDE integration, and a mature surrounding ecosystem of formatting, refactoring, and schema-comparison tools.

One Catch: It is tied to a commercial tool suite. The true cost is the broader dbForge licensing model, meaning you cannot buy the AI engine as a standalone, cheap utility.

SsmsAgentic -- agentic DBA work inside SSMS

A third-party VSIX that adds a Claude-powered agent pane to SSMS 22. Unlike completion tools, it executes the diagnostic work itself. It performs investigative DBA work by reading metadata, DMVs, execution plans and permissions, and then proposes actions for approval. It reuses your existing SSMS connection (including Entra ID) and your existing Claude plan, so there's no second AI bill. One-time license ($49–$199) after a 15-day trial.

It's the only SSMS-native option today doing actual investigative DBA work rather than suggesting code for you to run, and per-statement approval is the right security posture for agentic database access.

One Catch: Because it is third-party and not MSFT-backed, it requires the Claude CLI installed locally, SSMS v22+, and it is Windows only. Full disclosure: product reviews are still very thin out there, so these details come from the vendor's own site.

Claude Code + sqlcmd (or an MCP) -- the practitioner's agentic path

This one earned its spot from practitioner writing, not vendor marketing. Brent Ozar documented his own workflow using Claude Code against SQL Server and Azure SQL DB: at its simplest, Claude Code calls sqlcmd to run queries and read results, and as you get more advanced, you can wire up an MCP for richer database access. His advice for getting started safely: practice on an open-source repo you already use, like the FRK, Ola Hallengren's maintenance scripts, or DBAtools -- not your production estate.

You pick the model, you see the whole prompt, and nothing sits between you and the LLM rewriting your question. It's the same 'agentic' capability the shrink-wrapped tools sell, minus the black box.

One Catch: command-line workflow, more assembly required, and the same governance question as every agentic tool -- what you let it touch is on you.

Microsoft SQL MCP Server -- for building AI applications

MSFT's open-source MCP server (shipped via Data API Builder 1.7+) exposes a SQL database to any MCP-aware client -- Claude Desktop, VS Code, Cursor, or your own agent -- through a fixed set of typed CRUD tools with schema-level RBAC. This is the right shape when you're building an app or agent (ie., a support copilot that looks up orders) rather than assisting a human at a keyboard. A recent SQLServerCentral write-up found the setup much easier than expected, quirks and all.

It's free, open-source, runs on-prem, and it's the only path on this list that works from Mac and/or Linux.

One Catch: it's plumbing, not a product. No UI, no NL2SQL, DML only -- no DDL, no DMV access, no plan analysis. Setup means JSON config and usually hosting a container. And read 'The Ugly' before you hand it your data.

The Bad

To be clear, 'bad' here doesn't mean bad software. It means tools people are reaching for that are a poor fit for SQL Server work specifically.

Raw ChatGPT / generic chat with no database context

Still the most common choice, and still the most error-prone for production work. With no awareness of your schema, generic LLMs invent plausible table and column names, and they chronically cross dialects -- emitting LIMIT instead of TOP, or PostgreSQL :: casting instead of CAST(), even when told to target SQL Server. Fine for learning concepts but risky for anything you'll execute against a live database without careful review. Redgate's Simple Talk lands in the same place: you can trust AI with database issues, but verify every response it gives you. It's worth noting that the practitioner objection isn't to the LLMs themselves -- Brent Ozar's own preferred workflow sends well-built prompts to the LLM of your choice. The failure is with the missing context, not the model.

Amazon Q

Genuinely useful if your data lives in Redshift or Athena -- but close to useless for native SQL Server. No support for databases outside AWS, setup requires IAM and CLI configuration, and even within AWS its quality varies by service. If your estate is SQL Server on-prem or on Azure, skip it entirely.

Zero-setup 'paste your schema' web portals

Lightweight browser apps where you paste DDL and ask questions. The convenience is real, but dialect support skews heavily toward PostgreSQL and MySQL, accuracy drops sharply on complex queries, and manually pasting hundreds of lines of SQL Server schema into an unsecured browser text field gets old fast -- and should make your security team twitch. With no execution capability, you're copy-pasting in both directions, and the AI is blind to the things that actually matter: bottlenecks, waits, and lock escalation.

Generic text-to-SQL tools used for DBA work

Standalone tools like AI2SQL are legitimately good at their actual job -- letting analysts and PMs generate reporting queries from plain English against a connected schema. But they're query generators, not DBA tools. No SSMS integration, no DMV access, no execution-plan work, no migration or security-audit capability. Using them for SQL Server administration is a category error. Their published accuracy benchmarks were run against PostgreSQL, so the numbers don't transfer to T-SQL, which brings us to the ugly part.

The Ugly

The ugly here isn't referencing actual tools. It's the pieces inside the tools that are problematic and you need to know about.

SSMS Copilot rewrites your prompts -- and you can't see how

Brent Ozar's testing found that SSMS Copilot's advice quality lags well behind asking the same LLMs directly, and the culprit is the prompt Copilot wraps around your question. By 2026 he'd concluded that between SSMS interfering with his prompts and Copilot's shift toward usage-based pricing, the chat simply isn't worth using directly -- his workaround is building the prompt himself in T-SQL (the @AI parameter in sp_BlitzCache and sp_BlitzIndex) and sending it to whichever LLM he chooses. The kicker, courtesy of his readers: the chat logs -- including the hidden system prompt MSFT prepends to your questions -- sit in plain text under %localappdata%\SSMSCopilot. The tool most shops will standardize on is the one where you have the least visibility into what's actually being asked.

The benchmark shell game

Most 'AI for SQL' comparison content is written by vendors ranking themselves first, and the accuracy numbers that get repeated across these roundups trace back to benchmarks run against PostgreSQL schemas. PostgreSQL accuracy tells you nothing about whether a tool writes correct T-SQL, handles DMVs, or can read an execution plan. If a vendor quotes a percentage, you need to ask what engine it was measured on.

Your data goes along for the ride

Every one of these tools -- Copilot, agentic extensions, MCP pipelines -- ships your prompts, metadata, and query results to somebody's hosted model. The SQLServerCentral author who wired Claude Desktop to his ERP database said it very clearly: whoever uses the MCP has access to that data, and the data is sent to the provider for processing. He also had to disable Row-Level Security to get his demo working -- which is exactly the kind of shortcut that migrates quietly from demo to production. The permission model is on you. Dedicated SQL logins with minimal rights, no DELETE or DROP grants for agents, and a hard look at what your compliance posture allows to leave the building.

How to Choose

The pragmatic reality for most database teams is that you won't rely on a single tool. The common setup is a fast completion tool for raw typing speed, paired with one agentic path for the investigative work that used to consume your entire afternoon.

Your situation	Use this
Write T-SQL all day in SSMS	GitHub Copilot completions
Want AI to do the investigation (slow-query triage, index review, audits)	SsmsAgentic, or Claude Code + sqlcmd/MCP
Want full control of the prompt	Claude Code + sqlcmd, or @AI in sp_Blitz tools
dbForge shop	dbForge AI Assistant
Building an app or agent	SQL MCP Server
On Mac/Linux	SQL MCP Server + Claude Desktop or VS Code
Non-technical, just need a query	Schema-connected text-to-SQL tool

One Last Thought

After comparing today's AI tools for SQL Server, one thing became clear: there really isn't a single 'best' choice. Nearly every tool on this list can write T-SQL. The real differences are how well they understand SQL Server, how transparent they are about what they're doing, and how much control they leave in your hands.

If I were building a toolkit today, I'd install GitHub Copilot for everyday coding, use Claude Code for investigative work, choose dbForge AI if I already lived in the dbForge ecosystem, and reach for SQL MCP only when building AI applications. Together, those tools cover nearly everything a SQL Server DBA is likely to need today.

The best AI tool for SQL Server isn't the one that writes the most code. It's the one that helps you make better decisions while keeping you firmly in control.

Thursday, July 16, 2026

Don't Ask What the Feature Does. Ask What It Changes.

Sometimes it feels like we are drowning in announcements.

New AI feature. New compression algorithm. New optimizer enhancement. New DMV. New security bulletin. New Copilot capability.

Every release cycle feels the same.

"SQL Server now supports..."

"Microsoft announced..."

Great. But why should I care?

That isn't cynicism. It's the question every experienced DBA learns to ask, and one too many technical articles never answer. They stop after explaining what changed, but the smart DBA wants to know what it changes for them.

This post is about that gap -- and the mental framework senior DBAs use to close it.

The vendor's framing vs. yours

When MSFT (or any vendor) announces a feature, the announcement almost always makes one of four claims:

It makes something faster.
A new compression codec, a smarter optimizer, a parallelism improvement.
It makes something safer.
A hardened protocol, a new encryption option, a permission model change.
It makes something cheaper.
Less storage, fewer cores, lower licensing tiers doing more work.
It makes something easier.
A wizard, a new DMV, a Copilot integration, one less thing to script by hand.

Those four buckets are the vendor's framing, and the claims are usually accurate. But they're just answering the vendor's question, not the DBA's.

The vendor tells you what the feature does. The experienced DBA reads the same announcement and starts asking:

Why is it needed?
What problem was bad enough that MSFT built this? If I don't have that problem, I may not need the fix.
What does it impact?
Backups? Security? Licensing? The optimizer? Something I monitor, or something I've never had to think about?
Where is it relevant?
Which of my servers, which of my databases, which of my customers? All of them, one of them, none of them?
What does it break?
What did I configure, script, or assume years ago that this quietly steps on?

None of those questions are about the feature. They're about your environment. That's why two DBAs can read the same announcement and one shrugs while the other clears their calendar.

If every answer comes back empty, you file it and move on. If one comes back loaded, you've got some work to do.

What that looks like in practice

Abstract frameworks are cheap. Let's run some real announcements through the questions.

ZSTD backup compression

The announcement says: a new compression algorithm, better ratios, less CPU. The vendor framing: faster and cheaper.

What it means to you: your backup window has changed. What took ninety minutes now finishes in forty. Your maintenance window, log shipping, restore expectations, and everything scheduled around them were built around those ninety minutes. The feature matters because all of that may need to change with it.

Copilot in the database tooling

The announcement says: AI-assisted queries, natural language over your schema, productivity gains. The vendor framing: easier.

What it means to you: the AI inherits your permissions. There is no separate 'Copilot user' with a narrow grant. When it runs a query, it runs as you -- sysadmin, db_owner, whatever you're holding. Every conversation you've ever had about least privilege just gained a new participant, and it types very fast. 😆

RC4 Kerberos deprecation

The announcement says: a legacy encryption type is being disabled by default. The vendor framing: safer.

What it means to you: weak service accounts just became an immediate liability. That fifteen-year-old service account with the short password and the RC4-only configuration was always a problem in theory. Now it's a problem with a deadline. 'We'll get to it' just became 'better find it and fix it now'.

AI features inside the engine

The announcement says: AI built into the engine -- native vector search and T-SQL that calls external AI models to generate embeddings. The vendor framing here isn't faster/safer/cheaper/easier. It's a fifth claim: 'this makes something new possible'. Those deserve the most scrutiny of all.

What it means to you: your SQL Server may now initiate outbound Internet traffic. For twenty years, 'the SQL box is talking to the Internet' was a five-alarm indicator of risk. That is no longer the case. But your firewall and monitoring rules still assume it is. This isn't a feature to fear. It's one to understand. It's new outbound traffic that you have to manage -- deliberately, and on your terms.

Missing index recommendations

The announcement says: the engine surfaces indexes it thinks you should build. The vendor framing: easier.

What it changes for you: Almost nothing -- and that's the lesson. SQL Server is identifying opportunities, not prescribing changes. The missing index DMV output is a symptom log, not a change order. Hastily creating every recommended index is most likely going to hurt you a lot more than help. The right first response isn't action -- it's evaluation. Test what looks promising, skip what doesn't, and treat the rest as free intelligence about your workload.

The only filter that matters

Microsoft ships hundreds of pages of release notes every year. New engine features. New tooling. New DMVs. New security guidance. New Azure capabilities.

Nobody reads all of it.

Nobody needs to.

Experienced DBAs don't stay current by trying to memorize every announcement. They stay current by recognizing which announcements change their environment.

That's why those four questions matter.

Why is it needed?
What does it impact?
Where is it relevant?
What does it break?

If every answer comes back empty, file it away and move on.

If one comes back loaded, give it the attention it deserves and learn that feature thoroughly.

The goal isn't to know every new feature. It's to recognize when a new feature changes the way you operate.

The features will keep coming.

The questions stay the same.

Wednesday, July 15, 2026

SQL Server 2025 Can Call the Internet. So Can an Attacker.

Two months ago I wrote about Copilot in SSMS running as you until you fence it in. The lesson was that the security boundary is SQL Server's permission system, not the AI's intentions. Last month Brent dropped sp_BlitzUpdate, which uses SQL Server 2025's new ability to call external REST endpoints to pull code from GitHub and install it on startup. He noted you probably should not be grabbing code from the Internet and running it on your SQL Servers.

On June 10, 2026, Justin Kalnasy of SpecterOps published the other half of that sentence. If SQL Server 2025 can call the Internet, so can an attacker who owns a login on it. The full post is 'Oops, I Weaponized the Database: Abusing AI Features in SQL Server 2025'. Very good read. Here's what you need to know as a SQL Server professional.

The three features and what they actually allow

SQL Server 2025 shipped three AI-focused capabilities intended to support RAG pipeline workflows. All three are legitimate, documented, and working exactly as designed. That last part is relevant, because Microsoft has already been asked.

Feature	Intended use	What an attacker does with it
sp_invoke_external_rest_endpoint	Call an external REST API from T-SQL	POST up to 100MB of database contents per call to any HTTPS endpoint with a trusted cert
CREATE EXTERNAL MODEL	Register an embedding model location	Specify a UNC path to coerce NTLM authentication over SMB and capture the service account hash
AI_GENERATE_EMBEDDINGS	Send text to a model and receive a vector array	Disguise C2 command-and-control traffic as embedding telemetry, indistinguishable from legitimate model calls on the wire

None of these techniques give an attacker access to SQL Server. They become relevant after an attacker has already obtained a login with sufficient privileges. The concern is what those privileges now allow them to do.

The 100 MB payload limit is not much of a limitation. Kalnasy demonstrates that an attacker can split data across asynchronous requests and reconstruct it remotely, allowing very large databases to be exfiltrated in pieces.

The part that actually changes your threat model

SpecterOps built an 81-line T-SQL query that operates as a full command-and-control implant. It uses AI_GENERATE_EMBEDDINGS to check in with a C2 server, receive instructions encoded as synthetic vector arrays, XOR-decrypt them, execute commands, encode the output the same way, and send it back. Kalnasy includes a comparison of legitimate embedding traffic against C2 traffic in the original post. That image is the whole argument.

In Kalnasy's words, "Now ask yourself if an untrained analyst is really going to be able to spot the difference? Probably not".

This is the part that matters. For decades, egress web traffic from a database host was a reliable red flag. Traditionally, unexpected outbound Internet connections from a database server have always been a decent indicator that something should be investigated. SQL Server 2025 makes that signal far less reliable. As soon as an organization enables these AI features, that signal becomes much harder to trust, and any security tooling must now inspect query content and traffic payloads to verify risk. That is a much harder problem.

Kalnasy reported the NTLM coercion primitive specifically to Microsoft on April 20, 2026. On May 12, 2026, Microsoft determined the behavior did not represent a security boundary violation due to the nature of file path resolution, and it did not meet the bar for security servicing. Microsoft did not classify the reported behavior as requiring a security update. These features work as intended. The controls are yours to implement.

What the exfil actually looks like — safely

I am not reprinting a working exfil script. The SpecterOps repo is linked at the bottom if you want to lab it. What is useful here is seeing the shape so you could recognize it in an audit log.

These are descriptions of the three exfil patterns from the research so you know what to watch for:

Bulk table dump. Enable the stored procedure with sp_configure 'external rest endpoint enabled', 1, query a table with FOR JSON AUTO, store the result in a variable, and pass it as the @payload to sp_invoke_external_rest_endpoint. The entire table leaves in a single HTTPS POST. Credentials table, user table, whatever is accessible -- 100MB at a time.

File contents. Couple the same stored procedure with OPENROWSET(BULK ..., SINGLE_CLOB). Read any file the SQL Server service account can reach, including Windows hosts files, configuration files, anything on accessible shares, and POST it out the same way. The outbound connection originates from the SQL Server process, not a C2 agent.

Persistent trigger. In the SpecterOps PoC, Kalnasy demonstrates an AFTER INSERT trigger on a credentials table that calls sp_invoke_external_rest_endpoint on every write, POSTing new rows to a remote server in real time. As a proof of concept it is deliberately constructed, but the technique is sound, and a trigger is harder to evict than an agent because it lives in the database schema, not in memory. I'm including a query for you here to run now. You need to know if you have any triggers calling an external URL.

/* Detection: find triggers that call the REST endpoint.*/
SELECT
    OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
    OBJECT_NAME(t.object_id) AS TriggerName,
    OBJECT_NAME(t.parent_id) AS ParentTable,
    m.[Definition]
FROM sys.triggers t JOIN sys.sql_modules m
  ON m.object_id = t.object_id
WHERE m.definition LIKE '%sp_invoke_external_rest_endpoint%'
   OR m.definition LIKE '%AI_GENERATE_EMBEDDINGS%';

On a clean instance it should return nothing. If it returns rows, you should look into it.

What to alert on

SpecterOps provides specific detection guidance using Splunk SPL queries against SQL Audit or Extended Events output. The full queries are in the original post and worth bookmarking. These are the four signals to capture:

Signal	Why
external rest endpoint enabled = 1	The master switch. This appears in the SQL Server ERRORLOG natively, but you want it in your SIEM too.
CREATE / ALTER / DROP EXTERNAL MODEL	A new model registration, possibly pointing at a UNC path or hostile URL. Should not happen without a change ticket.
CREATE ASSEMBLY / AS EXTERNAL NAME	The advanced C2 variant loads a CLR implant in-memory from hex bytes. If you have CLR disabled and this fires, stop everything.
Trigger DDL on sensitive tables	The persistent exfil vector. Any new trigger on a credentials, user, or financial table warrants immediate review.

The two controls that actually stop it

Alerting only tells you it happened. These two things can be used to prevent it.

Pull sysadmin off your application service accounts. Kalnasy is direct: "Too often we find web applications with database connection strings containing an account with sysadmin roles. If your app is only doing standard database queries and writes you do NOT need sysadmin privileges". Every technique in the research assumes sysadmin or near-equivalent. Remove it and you have removed the ability to flip sp_configure, create external models, and deploy CLR assemblies. This is the same principle as the CopilotExec least-privilege pattern, applied one layer out.

Block internet-bound egress from your database hosts at the firewall. This is the single highest-leverage control and the one that ends the conversation regardless of what a login can do inside the engine. If the SQL Server host cannot reach arbitrary internet endpoints, a 100MB JSON POST has nowhere to go. If you are using AI features, host your models internally and allow only those specific destinations. If you are not using AI features, there is no legitimate reason for your database host to initiate HTTPS connections to the open Internet at all. That rule was always true. SQL Server 2025 just made enforcing it urgent.

-- There is no T-SQL for this. The fix is at the network edge:
--   DENY  outbound 443 from SQL Server host subnets to 0.0.0.0/0
--   ALLOW outbound 443 from SQL Server host subnets to your internal model hosts only
--   ALERT on any denied egress from a database host -- that alert IS the canary
--
-- If your SQL Server is already making internet HTTPS calls and you
-- didn't know about it, you have a problem that needs review.

Why this is different from the usual CVE post

There is no KB number here. There is no patch to wait for. There is no 'apply update and move on.' Microsoft reviewed the NTLM coercion primitive specifically, determined it works as designed, and closed the report. The rest of the techniques are in the same category: documented features, used as intended, just by the wrong person.

That shifts the responsibility entirely to you: your service account permissions, your egress rules, your audit configuration, and your analysts' ability to distinguish legitimate embedding traffic from traffic designed to look like it. Kalnasy puts it plainly: "Behavior takes time to baseline into modern security solutions, and the burden is being put solely on the administrator to add supplementary controls. Simply telling users to implement strong access controls and to monitor without clear guidance isn't good enough."

AI isn't the vulnerability. Your security boundaries are. SQL Server 2025 simply tests them in ways previous versions never could.

Tuesday, July 14, 2026

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

Wednesday, July 8, 2026

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.

Monday, July 6, 2026

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

Wednesday, July 1, 2026

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

Monday, June 29, 2026

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;

SQL Server Consulting

Friday, July 17, 2026

The Good

The Bad

The Ugly

How to Choose

One Last Thought

More to Read

Thursday, July 16, 2026

The vendor's framing vs. yours

What that looks like in practice

The only filter that matters

More to Read

Wednesday, July 15, 2026

The three features and what they actually allow

The part that actually changes your threat model

What the exfil actually looks like — safely

What to alert on

The two controls that actually stop it

Why this is different from the usual CVE post

More to Read

Tuesday, July 14, 2026

Two Things Happened Today, Not One

Six Things 2016 Users May Not Know

Find Your Exposure -- Right Now

The Dates That Matter Now

So What Do You Do This Week?

More to Read

Wednesday, July 8, 2026

Why doubling quotes stops working

demo time

The fix: keep it Unicode, and stop concatenating

Cleanup

Version notes

More to Read

Monday, July 6, 2026

The Groan

Prove It with A Demo

The Rewrites

Why This Is Getting Worse, Not Better

The Cleanup

More to Read

Wednesday, July 1, 2026

Challenge 1: The Two Functions That Are Not the Same

Challenge 2: The Filter That Returns Everything

Challenge 3: The Running Total That Lies

Challenge 4: Pin My Dive to the Top

Drop Your Answers Below! 👇

Monday, June 29, 2026

Curse 1: Integer Division Floors Your Percentage to Zero

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

Curse 3: Money in FLOAT Drifts

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

Curse 5: SUM Overflows the Column It Started In

Curse 6: DATEDIFF Overflows at Scale

In Summary

Cleanup

More to Read