Battle-Tested T-SQL Patterns: Real SQL Server Solutions Learned in Production

Over the years of working hands-on as a database developer and DBA, I’ve run into more production surprises than any single course or certification ever prepared me for — queries that mysteriously slow down for no visible reason, audit requirements that show up after a schema’s already frozen, reports that quietly regress the week after a routine deployment. None of these came with a textbook answer waiting on the shelf. Each problem forced me to roll up my sleeves, dig into the nitty-gritty details, and keep experimenting until the root cause finally revealed itself until something actually worked, then carrying that solution forward into the next system. This post is a collection of the patterns that came out of exactly that kind of trial-by-production — starting with the one that set the whole habit in motion: a T-SQL fix for a report that, without warning, stopped being fast.

 

The Report That Worked Fine Yesterday

It’s a familiar kind of alarm. A stored procedure that had run in under two seconds for months suddenly takes eleven. Nothing in the query changed. Nothing in the schema changed. The only thing different was which customer the report was run for that morning.

That’s parameter sniffing, and it’s one of those T-SQL problems that’s genuinely underexplained — most references mention it exists, show you the textbook definition, and move on. What they skip is the actual experience of chasing it: staring at an execution plan that looks completely reasonable, running the exact same query manually and getting a different plan than the one the procedure used, and slowly realizing the optimizer had cached a plan built for a rare, low-row-count parameter value and was now force-fitting every other execution through it.

It can easily leave even experienced DBAs scratching their heads because the query appears perfectly healthy one moment and inexplicably sluggish the next. The real culprit isn’t the query itself, but the cached execution plan chosen for an entirely different workload.

The fix, once you know what’s happening, is almost anticlimactic:

CREATE PROCEDURE dbo.GetOrdersByStatus
    @StatusID INT
AS
BEGIN
    SELECT * FROM Orders
    WHERE StatusID = @StatusID
    OPTION (RECOMPILE)
END

OPTION (RECOMPILE) tells SQL Server to throw away the cached plan and build a fresh one for this specific parameter value, every time. It costs a small amount of compilation overhead per execution, but for procedures where parameter values vary wildly in selectivity, that cost is nothing compared to an eleven-second query running for the wrong reason. I confirmed the diagnosis by checking sys.dm_exec_query_stats for the cached plan’s parameter values — seeing the compiled value sitting there, completely unrelated to what most executions actually needed, was the moment it clicked.

That one incident is what sent me back through years of scattered fixes I’d picked up the same way — not from a textbook, but from a production system quietly telling me something was wrong. Here are the ones that stuck.

 

The Patterns I Kept Reaching For

 

1. CROSS APPLY for “Top N Per Group” Queries

This one came out of a reporting requirement that sounds simple until you try to write it: “show me each customer’s most recent order.” My first instinct, like most people’s, was a correlated subquery or a ROW_NUMBER() wrapped in a filter. Both work. Neither is as direct as this:

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o

What makes CROSS APPLY different from a JOIN is that the right-hand side isn’t a static table — it’s re-evaluated per row of the outer table, with access to that row’s own columns. A JOIN structurally can’t do that. Once I understood CROSS APPLY as “run this per row” rather than “connect these two tables,” a whole class of awkward correlated-subquery problems stopped being awkward. Worth knowing: CROSS APPLY behaves like an inner join — a customer with zero orders disappears entirely. If you need to keep them (with NULLs filled in), OUTER APPLY is the one you want instead.

Once this concept clicks, you’ll often blaze through query designs that previously felt awkward or unnecessarily complicated. It isn’t magic—it simply offers a far more intuitive way of expressing row-by-row logic.

 

2. The OUTPUT Clause Instead of a Trigger

I inherited a system where every status change needed an audit trail, and the existing pattern for that was — like most legacy T-SQL I’ve walked into — a trigger. Triggers work, but they’re invisible in the moment you’re reading the actual UPDATE statement; the logging logic lives somewhere else entirely, waiting to surprise the next person who touches the table.

UPDATE Orders
SET StatusID = 3
OUTPUT deleted.OrderID, deleted.StatusID AS OldStatus, inserted.StatusID AS NewStatus
INTO OrderStatusAudit (OrderID, OldStatus, NewStatus)
WHERE OrderID = @OrderID;

The OUTPUT clause captures exactly what changed, in the same statement that made the change in a far more intuitive manner — no separate trigger to hunt down, no extra round trip. I switched to this pattern the first time I had to debug a trigger-based audit log that silently stopped firing after a schema change nobody documented. Since then, OUTPUT has been my default for anything transactional; triggers are still the right call for cross-table cascading logic, but for straightforward “log what changed,” this is simpler and far more visible.

 

3. Query Store to Catch a Regressed Execution Plan

This is the closest thing SQL Server has to a black box recorder, and it’s what finally gave me a real answer instead of a guess the next time a report “just got slow” after a deployment. Query Store tracks execution plan history over time, which means you can directly compare the plan a query used last week against the one it’s using today.

SELECT qt.query_sql_text, p.plan_id, p.last_execution_time
FROM sys.query_store_query_text qt
JOIN sys.query_store_plan p ON qt.query_text_id = p.query_id
ORDER BY p.last_execution_time DESC;

Before I started enabling this on production databases, “why did this get slow” investigations were largely archaeology — piecing together what might have changed from memory and deployment logs. Query Store turns that into a direct comparison: same query, different plan, here’s exactly when the switch happened. It’s by far one of the most valuable diagnostic features Microsoft has introduced into SQL Server. It’s been available since SQL Server 2016, and I still run into DBAs who’ve never turned it on.

 

4. Filtered Indexes for the Rows That Actually Matter

Most indexing advice stops at “add an index on the column you filter by.” What it usually skips is that if a huge portion of a table’s rows share a value you rarely query for — cancelled orders, inactive accounts, soft-deleted records — indexing all of them anyway just makes the index bigger and slower to maintain for no benefit.

CREATE INDEX IX_Orders_Active
ON Orders(OrderDate)
WHERE StatusID <> 4;  -- exclude 'Cancelled'

I started using filtered indexes after noticing an index that was nearly the size of the table itself, on a column where 40% of rows were cancelled orders nobody ever queried by date. Narrowing the index to only the rows that mattered cut its size substantially and made every seek against it faster, simply because there was less of it to scan.

 

5. System-Versioned Temporal Tables for Automatic History

Somewhere on my third manually-built “shadow” history table — the kind where you duplicate a table’s structure, add ValidFrom/ValidTo columns, and write trigger logic to populate it — I finally looked into whether SQL Server had a built-in answer. It does, and has since 2016.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    StatusID INT,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

SQL Server maintains the history table automatically — every update is captured without a single line of trigger code. I’d already hand-built this pattern at least twice before discovering it was a built-in feature, which is exactly the kind of thing that makes me suspect a lot of production databases are still doing it the manual way simply because nobody went looking.

For systems where data changes frequently, this eliminates a great deal of manual effort while reducing the risk introduced by constantly changing—or volatile—business requirements.

 

6. STRING_AGG Instead of the FOR XML PATH Hack

For years, concatenating grouped rows into a single delimited string meant reaching for the FOR XML PATH('') trick — a workaround so common it became the default answer, despite reading like it was never meant to be used this way (because it wasn’t).

SELECT CustomerID, STRING_AGG(ProductName, ', ') AS Products
FROM OrderDetails
GROUP BY CustomerID;

STRING_AGG has been available since SQL Server 2017, and it does in one clean function what the XML trick did through a syntax hack. I kept writing the old version out of habit long after upgrading, mostly because so much reference material online still teaches the workaround without mentioning it’s no longer necessary.

SQL Server continues to roll out capabilities that quietly replace many of the workarounds developers relied upon for years. Keeping pace with these improvements often means writing cleaner, simpler, and more maintainable T-SQL today.

 

A Few More Worth Knowing

These didn’t come from as memorable a war story, but they’ve earned a permanent place in how I write T-SQL:

• TRY_CONVERT — returns NULL instead of throwing an error on a failed conversion, which makes it genuinely useful as a data-cleaning strategy for messy imported data, not just a safer syntax choice.
• OPENJSON — shreds a JSON payload directly inside T-SQL, which matters more than it used to now that APIs routinely hand a stored procedure a JSON blob instead of individual parameters.
• TRY_CONVERT and OPENJSON together — I’ve used the pair specifically for validating and parsing JSON-sourced staging data in one pass, catching malformed rows without halting the whole batch.

 

Where This Leaves Me

None of these came from a course or a single reference article — they came from production systems doing something unexpected, one at a time, over years. That’s really the honest version of how most working knowledge gets built—not in one sitting, but pattern by pattern. Database development is a perpetual struggle between changing business requirements, evolving workloads, and ever-growing datasets, so learning never really stops.

If any of these techniques are new to you, start with the parameter-sniffing fix. It has solved performance issues in a myriad of production systems, and there’s a good chance it can save you hours of troubleshooting the next time an otherwise reliable stored procedure suddenly slows down.

 

Frequently Asked Questions

What is parameter sniffing in SQL Server, and why does it cause inconsistent performance?
Parameter sniffing happens when SQL Server caches an execution plan optimized for the first parameter value it sees, then reuses that same plan for subsequent executions with different, less-suitable parameter values — causing performance that varies dramatically depending on which value was used first.

When should I use CROSS APPLY instead of a JOIN?
Use CROSS APPLY when the right-hand side of the query needs to be re-evaluated per row of the outer table, such as calling a table-valued function per row or fetching the top N related rows per group — something a static JOIN cannot express.

Is OPTION (RECOMPILE) safe to use on every stored procedure?
Not universally — it adds compilation overhead on every execution, so it’s best reserved for procedures where parameter values vary widely in selectivity and a single cached plan genuinely can’t serve all cases well.

Do system-versioned temporal tables replace the need for manual audit logging?
For row-level history tracking, yes — SQL Server automatically maintains the history table without additional trigger code. They’re not a full substitute for application-level audit logs that need to capture who made a change or why, only what changed and when.

You may also be interested in reading these articles:

Automating SQL Server Health Checks with PowerShell (Invoke-Sqlcmd Walkthrough)
T-SQL Technique: Identifying Top-N Selling Products by Category Using RANK() and CTEs
T-Sql Trick: Quickly Resolve the Notorious “String or binary data would be truncated” Issue
T-SQL – REPLICATE() Function: Formatting and Masking Techniques
T-SQL Trick: Use Recursive CTE To Get Hierarchical Data