Filtered Indexes, Computed-Column Indexes, Unique Indexes, and Constraint Interactions

Learning outcomes

Some ServiceHub access paths are valuable only for a narrow subset of rows or for a normalized expression that is not stored directly. SQL Server provides filtered indexes and indexes on computed columns for these cases, while unique indexes can enforce an invariant and provide an access path. These features are powerful precisely because their matching rules are strict: predicate implication, parameterization, data-type conversion, determinism, precision, SET options, and NULL semantics all matter.

1. Filter only the rows the workload repeatedly needs

A filtered index is a nonclustered rowstore index with a WHERE predicate. It can have smaller storage and more focused statistics than a full-table index. In ServiceHub, dispatchers repeatedly search only open/escalated rows; an index for the active subset can therefore be more efficient than indexing closed history too.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab09') IS NULL EXEC(N'CREATE SCHEMA lab09 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab09.FilterProbe;CREATE TABLE lab09.FilterProbe(  work_order_id bigint IDENTITY PRIMARY KEY,  external_ref varchar(40) NULL,  status varchar(20) NOT NULL,  opened_at datetime2(3) NOT NULL,  customer_id int NOT NULL,  contact_phone varchar(30) NULL);;WITH n AS( SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab09.FilterProbe(external_ref,status,opened_at,customer_id,contact_phone)SELECT CASE WHEN n%17=0 THEN NULL ELSE CONCAT('WO-',n) END,       CASE WHEN n%20=0 THEN 'ESCALATED' WHEN n%4=0 THEN 'CLOSED' ELSE 'OPEN' END,       DATEADD(minute,n,'2026-01-01'),1+n%1000,       CASE WHEN n%9=0 THEN '(555) 010-' + RIGHT('0000'+CAST(n%10000 AS varchar(4)),4) ENDFROM n;GOCREATE INDEX IX_FilterProbe_ActiveON lab09.FilterProbe(status,opened_at)INCLUDE(customer_id)WHERE status IN ('OPEN','ESCALATED');GO

The query optimizer can use a filtered index when it can prove that a query's qualifying rows are contained in the filter. That proof can become difficult with parameters whose runtime values are not known or whose possible values include rows outside the filter.

2. Parameterization and predicate equivalence are part of the contract

A literal query for status='OPEN' clearly implies the active filter. A reusable parameter @status might be 'CLOSED', so a cached generic plan cannot blindly assume the filtered index contains every possible result. Options include separate statement shapes, recompilation where justified, or a broader index. Do not force a filtered index that is logically incomplete for a possible parameter value.

-- Literal: optimizer can reason about this exact value.SELECT work_order_id,customer_id,opened_atFROM lab09.FilterProbeWHERE status='OPEN'  AND opened_at >= '2026-01-05';GODECLARE @status varchar(20)='OPEN';SELECT work_order_id,customer_id,opened_atFROM lab09.FilterProbeWHERE status=@status  AND opened_at >= '2026-01-05';GO-- If business semantics truly justify per-execution compilation:DECLARE @status2 varchar(20)='OPEN';SELECT work_order_id,customer_id,opened_atFROM lab09.FilterProbeWHERE status=@status2  AND opened_at >= '2026-01-05'OPTION (RECOMPILE);GO

Inspect actual plans instead of assuming one plan. Recompile trades compilation CPU for value-specific optimization; it is not a universal filtered-index fix.

3. Computed columns can make transformations searchable

A query that repeatedly applies a deterministic transformation to a column can benefit from a computed column whose result is indexable. Indexing a computed column requires ownership, determinism/precision, data-type, and SET-option conditions. PERSISTED stores the computed value in the table and can expand the set of indexable deterministic expressions, but it also adds storage/write work.

ALTER TABLE lab09.FilterProbeADD phone_digits AS  REPLACE(REPLACE(REPLACE(REPLACE(contact_phone,'(',''),')',''),' ',''),'-','') PERSISTED;GOCREATE INDEX IX_FilterProbe_PhoneDigitsON lab09.FilterProbe(phone_digits)WHERE phone_digits IS NOT NULL;GOSELECT work_order_id,contact_phone,phone_digitsFROM lab09.FilterProbeWHERE phone_digits='5550101224';GOSELECT SESSIONPROPERTY('ANSI_NULLS') AS ANSI_NULLS,       SESSIONPROPERTY('ANSI_PADDING') AS ANSI_PADDING,       SESSIONPROPERTY('ANSI_WARNINGS') AS ANSI_WARNINGS,       SESSIONPROPERTY('ARITHABORT') AS ARITHABORT,       SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS CONCAT_NULL_YIELDS_NULL,       SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QUOTED_IDENTIFIER;GO

4. Unique indexes enforce a rule, including SQL Server NULL semantics

A unique index can enforce a candidate-key rule without being the table's primary key. In SQL Server a single-column unique index treats two NULLs as equal for uniqueness enforcement, so at most one NULL can exist. If the business rule is “many rows may have no external reference, but non-NULL references must be unique,” a filtered unique index is a better expression of the invariant.

CREATE UNIQUE INDEX UX_FilterProbe_ExternalRefON lab09.FilterProbe(external_ref)WHERE external_ref IS NOT NULL;GO-- This is allowed: many rows can remain NULL because they are outside the filter.INSERT lab09.FilterProbe(external_ref,status,opened_at,customer_id)VALUES (NULL,'OPEN',SYSUTCDATETIME(),9999),       (NULL,'OPEN',SYSUTCDATETIME(),9998);GO-- This would fail if an existing row already has WO-100:-- INSERT lab09.FilterProbe(external_ref,status,opened_at,customer_id)-- VALUES ('WO-100','OPEN',SYSUTCDATETIME(),9997);GO

A unique constraint typically creates a unique index, but the concepts are not identical. Constraints express relational integrity in schema semantics; indexes are physical access/enforcement structures. A foreign key, for example, does not automatically create an index on the referencing columns. Chapter 04's constraint-trust rules still apply.

5. Production judgment and cleanup

Use filtered indexes only when the subset is stable and important enough to justify a separate structure. Test real parameterization patterns. For computed columns, review determinism, precision, collation and implicit conversion behavior; index only expressions that reduce repeated runtime work or enable a genuinely useful access path. For uniqueness, encode the exact NULL/business semantics rather than relying on application checks.

DROP TABLE IF EXISTS lab09.FilterProbe;GO

Authoritative references

• Create filtered indexes — predicates, included columns and required SET options
• CREATE INDEX — computed-column and filtered-index requirements
• Indexes on computed columns — determinism, precision and SET options
• Index architecture and design guide — unique/filtered design guidance
• SQL Server 2025 build versions — servicing baseline