Red Flags in Your Query (T-SQL Tuesday #200)

I review a lot of code. Over the years I've built a mental checklist of things that make me go uh oh before I even run the query. Here's my shopping list.

AI-generated code

This one didn't exist three years ago. Now it's the first thing I look for.

To be clear, I'm not anti-AI. If the AI wrote clean code, I probably wouldn't even notice. The red flag isn't that AI generated it - it's the patterns that give it away. I recently saw a real case where someone needed to update a set of values. Simple enough, right? Here's what the AI-generated solution did:

Created a temp table with hardcoded values
Checked if the temp table it just created in step 1 is not empty
Looked for duplicates in the data that was manually typed in in step 1
Wrapped the whole thing in a batched update loop, even though the row count was nowhere near the lock escalation threshold

Now think about what happens if this breaks after step 3. Am I going to merge with an empty temp table and drop data from production? A simple update just became a branching logic puzzle with failure modes you can't even see.

That's the real problem with AI-generated SQL. When a junior developer writes something questionable, you can see the inexperience and your review instincts kick in. AI code looks polished and professional, which lulls you into a false sense of safety. Meanwhile, the over-engineering muddles the intent so much you can't tell what the code is actually trying to do. Ideally, we'd have tests to catch this, but for most database environments that's still a pipe dream. The review is often the safety net, and AI-generated code is making that net harder to use.

I've been spending time building SME-curated AI skills and have some great results, but most people have what is provided out of the box.

Scalar functions

I wrote a whole series about these, so I'll keep it brief. If I see a scalar function in a query, I already know two things:

Your query can't go parallel
Every row is processed one by one

That's not a minor inconvenience. That's a fundamentally different execution strategy that can turn a 2-second query into a 2-minute query. Go read the Scary Scalar Functions series if you want the gory details.

Non-sargable predicates

A predicate is SARGableSearch ARGument able
A query predicate that can use an index seek. Non-SARGable predicates force index scans. when the optimizer can use an index to satisfy it. When it's not, you get a scan. The rule is simple: the column has to stand alone on one side of the comparison.

Function trapping column

Column standing alone

Constant / Variable

✗ Non-SARGable (scan)

✓ SARGable (seek)

WHERE
  YEAR(OrderDate) = 2026

WHERE
  OrderDate >= '20260101'
  AND OrderDate <  '20270101'

WHERE
  TRY_CAST(MyColumn AS INT) = 85496554

WHERE
  MyColumn = TRY_CAST(85496554 AS VARCHAR)

WHERE
  ISNULL(MiddleName, 'E') = 'E'

WHERE
  (MiddleName IS NULL
  OR MiddleName = 'E')

WHERE
  DATEDIFF(MONTH,
    OrderDate,
    GETDATE()) >= 30

WHERE
  OrderDate <= DATEADD(MONTH, -30, GETDATE())

WHERE
  YEAR(OrderDate) = @year

WHERE
  OrderDate >= DATEFROMPARTS(@year, 1, 1)
  AND OrderDate <  DATEFROMPARTS(@year + 1, 1, 1)

WHERE
  DATEDIFF(DAY,
    CreatedDate,
    FinishedDate) > 40

No SARGable rewrite is possible because one of the columns will always be inside a function.

If a seek is truly necessary, consider an indexed computed column.

Look at the background highlights. On the left, the red highlight shows a function trapping a column inside it. The optimizer can't seek into that. On the right, the column stands alone (green highlight) and any function wraps only constants or variables. That's why there's no red background on the right side: moving the function away from the column is the whole fix.

The bad examples force SQL Server to evaluate the function for every single row. The good examples keep the column clean on one side, letting SQL Server use existing indexes for the comparison.

Of course, a seek only happens if a supporting index exists. But write SARGable predicates anyway. You never know when someone adds an index in the future, and you want the query to be ready for it.

NOLOCK on everything

The year 2001 called, they want their pessimistic concurrency back.

If I see NOLOCK sprinkled across every query, that tells me someone has a locking problem they never actually solved. NOLOCK doesn't fix locking issues. It just trades correctness for speed. You get dirty reads, skipped rows, even duplicate rows.

The real answer is Read Committed Snapshot Isolation. It's been available since SQL Server 2005. Twenty-one years. If you're still using NOLOCK as a band-aid in 2026, it's time to have a conversation with your DBA about enabling RCSI.

SELECT *

2001: A Space Odyssey meme - Dave Bowman staring in awe with caption 'MY GOD IT'S FULL OF SELECT *'

Before you come at me, I know SELECT * isn't universally evil. It's fine inside an EXISTS subquery, because the optimizer doesn't actually fetch the columns. It can be acceptable in ad-hoc exploration in SSMS. And I'll admit, on evolving schemas where columns change frequently, there's an argument for it.

But in production code? It pulls columns you don't need across the network. It prevents the optimizer from using covering indexes. And when someone adds a column to the underlying table, your view or app might break in fun and unexpected ways.

I actually have a lint rule for SELECT *, and the list of exceptions is longer than you'd think. But the default should be: spell out what you need.

Missing schema qualification

This one is sneaky.

When you write TableName instead of dbo.TableName, two things happen. First, SQL Server has to figure out which schema you mean, which causes plan cache pollution because users with different default schemas generate separate cached plans for the same query.

Second, and this is the one most people miss, it hides scalar functions. When you see OrderTotal in a query without schema qualification, is that a column? Or is it dbo.OrderTotal(), a scalar function disguised as a column? With schema qualification, the danger is visible. Without it, you have to check the table definition to know if you're dealing with a column or a performance trap.

A few extra characters. Just type them.

Table variables

I'll keep this one short. If you need a temporary result set, use a temp table. Table variables have no statistics, which means the optimizer assumes one row. If your table variable has a thousand rows, the optimizer still thinks it's one. The execution plan is built on a lie.

SQL Server 2019 introduced table variable deferred compilation, which helps. But a temp table still gives you statistics, parallelism, and a plan that actually reflects your data.

Too many CTEs

I actually love CTEs. But most people think they're materialized, as in: the engine runs the CTE once and reuses the result. That's not how it works. Every time you reference a CTE, it gets executed again. Reference the same CTE three times? That's three executions. If you need to reuse a result set, put it in a temp table.

And when I see a query with seven or eight CTEs stacked on top of each other, I start looking for more problems.

The wall of code

SSMS Map mode scrollbar showing a massive stored procedure - the minimap alone tells you this query has problems

This is the one you can spot before reading a single line.

If I open a query and the SSMS scrollbar shrinks to a tiny sliver, I know I'm in trouble. Five hundred lines of TSQL is almost never a good sign (exhibit A on the right). I have my scrollbar set to Map mode, so I can literally see the shape of the code before reading any of it. Huge monolithic blocks? No whitespace? No structure? No, thank you.

The map mode is like a chest X-ray for your query. You don't need to read every line to know something's wrong.

The kitchen sink query

One stored procedure to rule them all. "Find me a customer by surname." "Now by email." "Now by phone number." "Now by address, but partial match." You pass in a dozen optional parameters and the query tries to serve every possible combination of filters in a single plan.

That can't have stable performance by definition. The optimizer compiles a plan for the first set of parameters that comes in, and that plan gets cached and reused for everyone else. If the first caller searched by CustomerID (unique, 1 row, nested loops) and the next caller searches by partial surname (thousands of rows), the second caller gets a plan built for 1 row. Enjoy your nested loops on a table scan.

The usual fixes are a set of trade-offs:

Dynamic SQL with sp_executesql builds a query with only the filters you actually need. You get good plans every time, but you trade readability and observability. Debugging dynamic SQL is not fun
OPTION (RECOMPILE) forces a fresh plan every execution. Great for infrequent queries. Expensive if the proc runs 100 times a second
Split into multiple procedures: a happy-path proc for the common case and a separate one for the complex filters. Clean plans, clean code. But beware the split brain: someone updates one proc and forgets about the other

There's much more I could add to this list. But these are the big tells. The ones that make me stop scrolling and start paying attention.

Thank you for reading