Indexed Computed Columns: Speeding Up Code You Cannot Change

The vendor query wraps Email in UPPER and scans a large table. Indexed computed columns can give that unchanged expression an access path, provided SQL Server can match it and the index meets its rules. The technique is useful when the application SQL is outside your control.

Where Indexed Computed Columns Help

A function around a filtered column can make a normal index on the base column less useful. The optimizer sees the result of UPPER(Email), not a simple range on Email. Indexed computed columns store an expression that matches the query, so the engine can seek the expression's values. I inspect the actual plan and the predicate before adding one. Sometimes the existing collation already makes an uppercase comparison unnecessary, but vendor SQL still sends it.

Check the exact data type, collation, and expression. UPPER(Email) and UPPER(CONVERT(nvarchar(256), Email)) are not automatically the same match. A cast on the constant side can also change the expression tree. Capture the statement the application sends, including parameters and SET options. What expression does the plan actually evaluate?

Build a Disposable UPPER Example

The sample uses a temporary table so you can test without changing a vendor schema. The computed column is defined with the same expression as the query, and the index stores that result. Populate enough rows for an index to be attractive. A small sample can still scan because scanning ten pages is cheaper than seeking and looking up each row.

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
CREATE TABLE #VendorEmail
(
    CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
    Email varchar(120) NOT NULL,
    EmailUpper AS UPPER(Email)
);
WITH n AS
(
    SELECT TOP (50000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT #VendorEmail (CustomerID, Email)
SELECT CONVERT(int, rn),
       CONCAT('customer', rn, '@example.test')
FROM n;
CREATE INDEX IX_VendorEmail_EmailUpper
ON #VendorEmail (EmailUpper);

The inserted domain is sample data only. Keep the query and the index in the same test session. In a real table, calculate the additional index storage and write cost. Every insert or Email update must maintain the expression index. That work is justified by measured read savings, not by the novelty of making an unchangeable query seek.

Test the Query Without Naming the New Column

Run the vendor-shaped expression against the sample. Do not rewrite the predicate to EmailUpper for the test; that would avoid testing the optimizer's expression matching. Capture the actual execution plan and STATISTICS IO. Compare logical reads and row estimates with and without the new index in a test copy. The optimizer can choose a scan for a broad filter, which is a valid cost choice.

SET STATISTICS IO ON;
SELECT CustomerID, Email
FROM #VendorEmail
WHERE UPPER(Email) = 'CUSTOMER12345@EXAMPLE.TEST';
SET STATISTICS IO OFF;

Look for an Index Seek on IX_VendorEmail_EmailUpper and inspect its Seek Predicates. If the plan scans, check expression equivalence, SET options, collation, parameter type, and selectivity. Do not add an index hint to make the demonstration look successful. A forced seek that reads more pages is not a win.

Check the output columns too. Selecting CustomerID and Email can require a lookup from an index that stores only EmailUpper. For one matching row that is cheap. For a broad search it can dominate the plan and make a scan reasonable. A covering index can include Email, but every included byte increases storage and maintenance. Test the exact vendor projection, not a SELECT COUNT query that can use a narrower path. I also compare the predicate value under the column collation. Case folding rules are linguistic rules, and a match that looks obvious in English can behave differently under another collation. Correct results come before a faster plan.

Handle CAST of a Date Separately

A vendor query that filters on CAST(OrderDate AS date) has a similar pattern. A computed OrderDay AS CAST(OrderDate AS date) can be indexed, then tested with the unchanged expression. SQL Server also has optimizer behavior for some date conversions that can already use an ordinary index, so measure before adding storage. The date type of OrderDate and the exact cast matter.

CREATE TABLE #VendorOrders
(
    OrderID int NOT NULL PRIMARY KEY,
    OrderDate datetime2(3) NOT NULL,
    OrderDay AS CAST(OrderDate AS date)
);
CREATE INDEX IX_VendorOrders_OrderDay
ON #VendorOrders (OrderDay);
SELECT OrderID
FROM #VendorOrders
WHERE CAST(OrderDate AS date) = '20250115';

This empty lab table demonstrates the syntax, not a performance result. Populate representative rows before comparing plans. If you control the SQL, a half-open range on OrderDate can be simpler and avoid the extra index. When you cannot change the SQL, the computed expression is a useful candidate. Keep one row of evidence showing the original predicate and the new index access.

Rules Indexed Computed Columns Must Satisfy

An indexed computed column must meet determinism, precision, ownership, and data type requirements. UPPER over a local string and CAST from datetime2 to date are suitable starting examples, but test the exact expression. COLUMNPROPERTY can report IsDeterministic and IsPrecise. Imprecise expressions can have restrictions as key columns. Avoid functions that depend on current time or external state. COLUMNPROPERTY looks up the object in the current database, so the check below switches to tempdb first. Run from another database, it returns NULL for a temporary table.

USE tempdb;
SELECT COLUMNPROPERTY(OBJECT_ID(N'tempdb..#VendorEmail'),
                      N'EmailUpper', N'IsDeterministic')
       AS is_deterministic,
       COLUMNPROPERTY(OBJECT_ID(N'tempdb..#VendorEmail'),
                      N'EmailUpper', N'IsPrecise')
       AS is_precise;

Required session settings matter both when creating the index and when modifying rows. ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER need to be ON; NUMERIC_ROUNDABORT needs to be OFF. A SELECT under incompatible settings can ignore the computed-column index. I check the application's actual connection settings, not only my SSMS window.

Keep the Fix Narrow

Use the smallest computed index that addresses the expensive predicate and required output. Include extra columns only when lookups dominate and the write cost is acceptable. Test inserts and updates as well as reads. If a vendor upgrade changes the expression, the match can disappear even though the index remains. Add that query to the upgrade test list.

I also compare the alternative of an ordinary index or a case-insensitive collation when those choices are under your control. Changing collation has broad semantics and is not a casual tuning step. Indexed computed columns are a targeted answer to fixed SQL. Its success is visible in the plan and read counts, not in the fact that CREATE INDEX completed. If a test table seeks but the vendor table scans, inspect row counts, statistics, parameter types, and application SET options before concluding that expression matching failed. Those differences are part of the workload. Record the DDL and the application plan together so the index is not left behind after the vendor query changes.

Related reading on this blog: Computed Column and Compute Scalar Operators and Avoid Functions in the WHERE Clause for Performance.

A computed-column index is not a guaranteed seek, it is useful when the expression and workload match.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.