Never Put Functions in WHERE Clauses: Why Your Indexes Are Ignored

The Rule

Do not apply functions to indexed columns in WHERE clauses. Ever.

When you do, SQL Server can't use the index. It must scan every row instead of seeking to the rows it needs.

Why This Matters

Index Seek: SQL Server jumps directly to the rows you need. 10 rows returned = 10 pages read.
Index Scan: SQL Server reads every row in the table, then filters. 1 million rows stored = 1 million rows examined.

The difference is 100,000x slower.

The Problem Explained

Why Indexes Fail with Functions

An index stores sorted values of a column:

-- Index on Orders.OrderID
-- Storage: [1001], [1002], [1003], ..., [999999]
-- Sorted, so binary search works fast

When you apply a function, the optimizer can't use the index because it doesn't know the function result at compile time:

-- Query with function
WHERE YEAR(OrderDate) = 2024

-- The index is on OrderDate, but not on YEAR(OrderDate)
-- SQL Server must: for each row, calculate YEAR(OrderDate), then check if it equals 2024
-- This forces a table scan

The Query Optimizer's Dilemma

When optimizing a query, SQL Server decides: "Use an index or scan the whole table?"

For direct column comparisons, the choice is clear:

-- Direct comparison → Use index
WHERE OrderID = 1001
-- Index has sorted OrderIDs, seek is fast

For function-based comparisons, the choice is impossible:

-- Function comparison → Can't use index
WHERE YEAR(OrderDate) = 2024
-- Index doesn't exist for YEAR(OrderDate)
-- Must evaluate the function for every row

Common Functions That Disable Indexes

Function	Problem	Example	Fix
`YEAR()`, `MONTH()`, `DAY()`	Extracts part of date	`WHERE YEAR(OrderDate) = 2024`	`WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'`
`UPPER()`, `LOWER()`	Changes string case	`WHERE UPPER(Name) = 'JOHN'`	`WHERE Name = 'John'` (or case-insensitive collation)
`SUBSTRING()`	Extracts partial string	`WHERE SUBSTRING(Email, 1, 5) = 'admin'`	`WHERE Email LIKE 'admin%'`
`CAST()`, `CONVERT()`	Type conversion	`WHERE CAST(OrderID AS VARCHAR) = '1001'`	`WHERE OrderID = 1001`
`ISNULL()`, `COALESCE()`	Null replacement	`WHERE ISNULL(Status, 'Unknown') = 'Active'`	`WHERE Status = 'Active' OR Status IS NULL`
`DATEDIFF()`	Date arithmetic	`WHERE DATEDIFF(DAY, OrderDate, GETDATE()) < 30`	`WHERE OrderDate > DATEADD(DAY, -30, CAST(GETDATE() AS DATE))`
`ABS()`, `ROUND()`	Math operations	`WHERE ABS(Amount) > 100`	`WHERE Amount > 100 OR Amount < -100`

Real Examples: Broken vs. Fixed

Example 1: Date Extraction

-- BROKEN (Full table scan)
SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2024;

-- FIXED (Index seek)
SELECT * FROM Orders
WHERE OrderDate >= '2024-01-01'
  AND OrderDate < '2025-01-01';

Impact: 10 million orders: 2 seconds → 50 ms (40x faster)

Example 2: String Case

-- BROKEN (Full table scan)
SELECT * FROM Users
WHERE UPPER(Email) = 'JOHN@EXAMPLE.COM';

-- FIXED (Index seek, if case-insensitive collation)
SELECT * FROM Users
WHERE Email = 'John@Example.com';  -- Collation handles case
-- Or:
SELECT * FROM Users
WHERE Email = N'John@Example.com' COLLATE SQL_Latin1_General_CP1_CI_AS;

Example 3: Date Arithmetic

-- BROKEN (Full table scan)
SELECT * FROM Orders
WHERE DATEDIFF(DAY, OrderDate, GETDATE()) < 30;

-- FIXED (Index seek)
SELECT * FROM Orders
WHERE OrderDate > DATEADD(DAY, -30, CAST(GETDATE() AS DATE));

Example 4: Type Casting

-- BROKEN (Full table scan)
SELECT * FROM Orders
WHERE CAST(OrderID AS VARCHAR) LIKE '100%';

-- FIXED (Index seek)
SELECT * FROM Orders
WHERE OrderID >= 100 AND OrderID < 110;  -- Or use BETWEEN

Example 5: Substring with LIKE

-- BROKEN (Full table scan)
SELECT * FROM Users
WHERE SUBSTRING(Email, 1, 5) = 'admin';

-- FIXED (Index seek possible with LIKE)
SELECT * FROM Users
WHERE Email LIKE 'admin%';  -- Left-anchored LIKE can use indexes

Verifying Index Usage

Check the Execution Plan

-- Enable execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Broken query
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- Look for:
-- "Table Scan" → Bad, full scan
-- "Logical reads: 5000" → Many reads, inefficient

-- Fixed query
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

-- Look for:
-- "Index Seek" → Good, targeted access
-- "Logical reads: 50" → Few reads, efficient

Edge Cases: When Functions Are Okay

1. Filtered Indexes

If you create an index specifically for a function, it's okay to use that function in WHERE:

-- Create filtered index on UPPER(Email)
CREATE INDEX idx_email_upper ON Users(Email)
WHERE UPPER(Email) = Email;  -- Only uppercase

-- Now this query can use the index
SELECT * FROM Users WHERE UPPER(Email) = 'JOHN@EXAMPLE.COM';

2. Computed Columns with Persisted Indexes

-- Create computed column
ALTER TABLE Orders ADD YearOrdered AS YEAR(OrderDate) PERSISTED;

-- Create index on computed column
CREATE INDEX idx_year ON Orders(YearOrdered);

-- Now this query can use the index
SELECT * FROM Orders WHERE YearOrdered = 2024;

3. Functions on the Right Side of Comparison

-- Bad
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- Okay (function on right side, not on indexed column)
SELECT * FROM Orders WHERE OrderDate >= CAST('2024-01-01' AS DATE);

Real-World Impact

The Slowdown Story

A company had a production report that took 45 seconds to run:

-- Original query
SELECT * FROM Transactions
WHERE YEAR(TransactionDate) = 2024
  AND Status = 'Completed';

They checked the index: exists on TransactionDate. But the YEAR() function forced a full table scan of 500 million rows.

Fix:

-- Rewritten
SELECT * FROM Transactions
WHERE TransactionDate >= '2024-01-01'
  AND TransactionDate < '2025-01-01'
  AND Status = 'Completed';

Result: 45 seconds → 0.5 seconds (90x faster)

Why Developers Do This

Functions in WHERE are common because:

Convenience: YEAR(OrderDate) = 2024 is simpler than date range comparisons
Readability: The intent is clearer with YEAR()
Habit: Legacy code from databases with different optimization rules
Lack of awareness: Developers don't always know about index impact

All valid reasons, but the performance cost is too high.

Best Practices

1. Avoid Functions on Indexed Columns

Never write: WHERE function(column) = value

Write instead: WHERE column = value or equivalent.

2. Use Computed Columns for Complex Expressions

If you frequently query YEAR(OrderDate), create a persisted computed column and index it:

ALTER TABLE Orders ADD YearOrdered AS YEAR(OrderDate) PERSISTED;
CREATE INDEX idx_year ON Orders(YearOrdered);

3. Pre-Calculate in Application Code

If you must use a function, calculate it outside the WHERE clause:

-- In application code
var year = DateTime.Now.Year;
var query = "SELECT * FROM Orders WHERE YEAR(OrderDate) = @Year";

-- Better (pass the date range instead)
var startDate = new DateTime(year, 1, 1);
var endDate = new DateTime(year + 1, 1, 1);
var query = "SELECT * FROM Orders WHERE OrderDate >= @Start AND OrderDate < @End";

4. Use Indexed Views for Complex Transformations

For complex functions, create an indexed view that SQL Server can use:

CREATE VIEW vw_OrdersWithYear WITH SCHEMABINDING AS
SELECT OrderID, OrderDate, YEAR(OrderDate) AS YearOrdered, Status
FROM dbo.Orders;

CREATE UNIQUE CLUSTERED INDEX idx_vw ON vw_OrdersWithYear(YearOrdered);

-- Query now uses indexed view
SELECT * FROM vw_OrdersWithYear WHERE YearOrdered = 2024;

5. Profile Before Optimization

Always check the execution plan before and after changes:

-- Before
STATISTICS IO ON;
SELECT COUNT(*) FROM Orders WHERE YEAR(OrderDate) = 2024;  -- Note logical reads

-- After
STATISTICS IO ON;
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';
-- Compare logical reads

The Verdict

Functions in WHERE clauses are performance killers. Avoid them on indexed columns. If you must use functions, create computed columns, indexed views, or filtered indexes to support them.

Remember:

Index Seek (good) vs. Index/Table Scan (bad)
Functions force scans
Rewrite using date ranges, pattern matching, or computed columns
Always check execution plans

One line of code changed from WHERE YEAR(OrderDate) = 2024 to WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01' can transform a 45-second report into a half-second query. That's worth learning the rule.

← Back to Blog