#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