Unique & Filtered Indexes

Two players can't wear the same jersey number in one squad, and most reporting only cares about the big scorers. SQL Server has an index for each of these jobs: a unique index that refuses duplicates, and a filtered index that only stores the rows you care about.

Definition

A unique index is an index that guarantees no two rows have the same value (or the same combination of values) in the indexed column(s). Any INSERT or UPDATE that would create a duplicate fails with an error.

A filtered index is a non-clustered index with a WHERE clause, so it contains only the rows that meet a condition.

• Guarantees — a unique index is a rule (data integrity), not only a speed-up.
• Combination of values — a unique index on (team, jersey_no) allows jersey 18 in RCB and in MI, but not twice in RCB.
• Only the rows — fewer rows → a smaller index, less storage, cheaper maintenance, faster seeks.

Syntax

-- Unique index
CREATE UNIQUE [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column1, ...);

-- Filtered index
CREATE NONCLUSTERED INDEX index_name
ON table_name (column1, ...)
WHERE condition;

Example — unique index on jersey numbers

CREATE UNIQUE INDEX IX_players_jersey ON players (jersey_no);

Benefits: (1) enforces uniqueness and (2) slightly increases query performance — the optimizer knows at most one row can match, so it stops after the first hit.

Note: A PRIMARY KEY creates a unique (clustered) index automatically, and a UNIQUE constraint creates a unique non-clustered index behind the scenes.

Example — filtered index for big scorers

CREATE NONCLUSTERED INDEX IX_players_big_scorers
ON players (runs)
WHERE runs >= 500;

A query like WHERE runs >= 500 can use this small index. A query like WHERE runs > 100 cannot (it needs rows the index doesn't contain).

Benefits: targeted optimisation and less storage (fewer rows in the index).

Rule: a filtered index cannot be CLUSTERED or COLUMNSTORE — it is always non-clustered rowstore. Its WHERE must be simple comparisons (=, <>, >, <, IN, IS NULL, IS NOT NULL) joined with AND — no OR, no functions.

How to choose the right index

Common mistakes

• ❌ Creating a unique index on a column that already has duplicates. ✅ The CREATE itself fails — clean the duplicates first.
• ❌ Expecting a filtered index on runs >= 500 to help WHERE runs >= 100. ✅ The query's condition must fall inside the filter.
• ❌ Assuming a unique index allows many NULLs in SQL Server. ✅ SQL Server treats NULLs as equal in a unique index — only one NULL is allowed. Use a filtered unique index WHERE col IS NOT NULL to allow many.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: What is the difference between a primary key and a unique index? A: Both enforce uniqueness. A primary key also forbids NULLs, there can be only one per table, and in SQL Server it creates a clustered index by default. You can have many unique indexes, they are non-clustered by default, and they allow one NULL (in SQL Server).