Bits of .NET

🗄️ You Added an Index on the Computed Column – SQL Server Refuses It

A computed column built from a simple expression looks like any other column and should, in theory, be indexable like one – and creating an index on it fails outright with an error about the column not being deterministic or precise enough, despite the expression looking perfectly ordinary.

🐞 The Problem

ALTER TABLE Orders ADD FullName AS (FirstName + ' ' + LastName);

CREATE INDEX IX_Orders_FullName ON Orders(FullName);
-- Msg 2729: Column 'FullName' in table 'Orders' is not
-- allowed to participate in the index because it is
-- non-deterministic or imprecise.

🔍 Why This Happens

By default, a computed column is VIRTUAL - its value is calculated
fresh on every read, not physically stored on disk. SQL Server
requires a computed column to be marked PERSISTED (physically
stored and updated automatically whenever the underlying columns
change) before it can be indexed, since an index needs a stable,
physically stored value to point to. Beyond that, the expression
itself must also be deterministic (the same inputs always produce
the exact same output) - some string/date functions don't qualify,
which is the other common reason this same error appears.

✅ The Fix: Mark the Computed Column PERSISTED Before Indexing

• Add PERSISTED to the computed column definition – this tells SQL Server to physically store the calculated value and keep it updated automatically, satisfying the requirement for indexing.
• Confirm the expression itself is actually deterministic – simple concatenation and arithmetic qualify, but many system functions (like GETDATE()) do not, and no amount of PERSISTED will make a non-deterministic expression indexable.
• Consider whether persisting the column is worth the small write-time cost (every insert/update now also computes and stores this value) versus just querying with the expression directly and letting an index on the underlying source columns handle it instead.

📦 Correct Version

ALTER TABLE Orders ADD FullName AS (FirstName + ' ' + LastName) PERSISTED;

CREATE INDEX IX_Orders_FullName ON Orders(FullName);
-- Now succeeds - the column is physically stored and deterministic.