SQL Server Interview Question #238

What is an indexed computed column?

Advanced Indexing Senior Advanced

Detailed Explanation

An eligible computed column can be indexed so SQL Server can efficiently search or order by a derived expression.

This can be useful when applications repeatedly filter by a deterministic calculation that would otherwise require computing a function for many rows. SQL Server imposes determinism, precision, SET-option, ownership, and data-type requirements for indexing computed columns.

Before adding one, verify that the expression and workload justify the extra storage and maintenance.

Code Example

ALTER TABLE dbo.Users
ADD NormalizedEmail AS LOWER(Email) PERSISTED;

CREATE INDEX IX_Users_NormalizedEmail
ON dbo.Users(NormalizedEmail);