Unique Index Performance: What a UNIQUE Index Saves

Unique index performance gains come from what the optimizer learns, not from reading fewer pages. A test with 200,000 coupon codes shows the same page reads, one operator fewer and far less CPU.

The Claim to Test

A nonclustered index can be unique when its column never repeats. At one client, most nonclustered indexes were not marked unique, and code changes were not allowed. After a talk with the developers, the team changed nearly 70 percent of them to unique nonclustered indexes. The final test showed about 23 percent better performance.

That raises a fair question. Does a unique index read fewer pages, or does it do less work on the pages it reads? Page counts matter first. If two indexes differ in size, a scan reads a different number of pages, and the comparison means little. The demo separates pages from work. Both tables hold 200,000 distinct codes. One index is plain, and the other is unique.

IF DB_ID(N'UniqueIndexDemo') IS NULL CREATE DATABASE UniqueIndexDemo;
GO
USE UniqueIndexDemo;
GO
DROP TABLE IF EXISTS dbo.CouponsPlain;
DROP TABLE IF EXISTS dbo.CouponsUnique;
CREATE TABLE dbo.CouponsPlain  (CouponID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Code varchar(64) NOT NULL);
CREATE TABLE dbo.CouponsUnique (CouponID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Code varchar(64) NOT NULL);
CREATE INDEX IX_CouponsPlain_Code ON dbo.CouponsPlain (Code);
CREATE UNIQUE INDEX IX_CouponsUnique_Code ON dbo.CouponsUnique (Code);
GO
INSERT INTO dbo.CouponsPlain (Code)
SELECT TOP (200000) CONCAT('SPRING', RIGHT(CONCAT('000000', n), 6))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x
ORDER BY n;
INSERT INTO dbo.CouponsUnique (Code)
SELECT Code FROM dbo.CouponsPlain;

Count the Pages First

Pages decide how much a scan reads. This query lists the pages of each new index. It works the same way on any pair of indexes in your own databases.

SELECT OBJECT_NAME(ps.object_id) AS TableName, i.name AS IndexName, i.is_unique AS IsUnique,
       ps.used_page_count AS Pages, ps.row_count AS RowsInIndex
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id IN (OBJECT_ID(N'dbo.CouponsPlain'), OBJECT_ID(N'dbo.CouponsUnique'))
  AND i.index_id > 1
ORDER BY TableName;

Both indexes use 650 pages. In this test the unique flag did not shrink the index. A plain index adds the row locator to its key, and a unique index keeps the locator outside the key. That can change the size of the upper levels by a few pages. A variant on a heap, which is not in the demo, gave 752 pages against 750.

Run the Same Query on Both

Each statement counts the distinct codes. SET STATISTICS IO reports the pages read on the Messages tab.

SET STATISTICS IO ON;
SELECT COUNT(*) FROM (SELECT DISTINCT Code FROM dbo.CouponsPlain) AS d;
SELECT COUNT(*) FROM (SELECT DISTINCT Code FROM dbo.CouponsUnique) AS d;
SET STATISTICS IO OFF;

The reads are equal, so the unique index did not read fewer pages. The plan shows what changed. SET SHOWPLAN_TEXT prints the plan without running the query.

SET SHOWPLAN_TEXT ON;
GO
SELECT DISTINCT Code FROM dbo.CouponsPlain;
GO
SELECT DISTINCT Code FROM dbo.CouponsUnique;
GO
SET SHOWPLAN_TEXT OFF;

A plain index cannot promise that values never repeat. DISTINCT must therefore remove repeats, and the plan adds a Stream Aggregate. A unique index makes that promise, so the optimizer drops the aggregate. The same happens for GROUP BY Code.

Measure the CPU

The aggregate costs processor time, not pages. This batch runs each statement 30 times and reads the average CPU from the plan cache.

DECLARE @i int = 0, @c int;
WHILE @i < 30
BEGIN
    SELECT @c = COUNT(*) FROM (SELECT DISTINCT Code FROM dbo.CouponsPlain) AS loopRun;
    SELECT @c = COUNT(*) FROM (SELECT DISTINCT Code FROM dbo.CouponsUnique) AS loopRun;
    SET @i += 1;
END;

SELECT CASE WHEN SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, 400) LIKE N'%CouponsPlain%' THEN N'Plain' ELSE N'Unique' END AS Variant,
       qs.execution_count AS Runs,
       qs.total_worker_time / qs.execution_count AS AvgCpuMicroseconds,
       qs.total_logical_reads / qs.execution_count AS AvgReads
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%AS loopRun%'
  AND st.text NOT LIKE N'%dm_exec_query_stats%'
  AND qs.statement_start_offset >= 0
ORDER BY Variant;

The table shows one run. CPU differs on every machine and every run, while the reads do not. In repeated runs on the test server, the plain statement needed roughly four times the CPU of the unique one. The demo shows one way a unique index helps. The client’s own test measured only the end result, so the reason for their gain is not known.

Check the Data, Then Convert

Unique index performance depends on the data first. A column qualifies when its distinct count equals its row count. The first query compares the two numbers on the plain table, and the second lists any repeated code.

SELECT COUNT(*) AS TotalRows, COUNT(DISTINCT Code) AS DistinctCodes FROM dbo.CouponsPlain;

SELECT Code, COUNT(*) AS Copies FROM dbo.CouponsPlain GROUP BY Code HAVING COUNT(*) > 1;

The numbers match, and the second query returns no rows. The column qualifies today. An existing index converts in place with DROP_EXISTING. The rebuild reads the whole index, so schedule it like any other index rebuild.

CREATE UNIQUE INDEX IX_CouponsPlain_Code ON dbo.CouponsPlain (Code) WITH (DROP_EXISTING = ON);

SELECT name, is_unique AS IsUnique
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.CouponsPlain') AND name = N'IX_CouponsPlain_Code';

The Price of Unique

The conversion turns a fact about today into a permanent rule. A unique index must describe the data. Declare it only when the business rule says the values never repeat. If a duplicate exists, the CREATE UNIQUE INDEX statement fails with Msg 1505, which is why the check comes first. The database enforces the rule on every insert, as this statement shows.

INSERT INTO dbo.CouponsUnique (Code) VALUES ('SPRING000001');

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.CouponsUnique' with unique index 'IX_CouponsUnique_Code'. The duplicate key value is (SPRING000001).
The statement has been terminated.

You could argue that the gain only helps DISTINCT and GROUP BY. That is right for this demo. A query that never removes repeats sees no difference. A unique index earns its place on a column that the data model already calls unique. A coupon code or an email address fits.

Two more details belong in the price. A unique index treats NULL as one value. A second NULL in a nullable column fails with the same error. Every insert also checks for a duplicate, so measure a heavy load before and after the change.

What to Remember

Unique index performance comes from the promise, not from smaller pages. Check the plan for an aggregate or a sort that a unique index would remove. Check the data first, because a wrong promise fails on the first duplicate.

Measure before you convert many indexes. Compare reads, CPU and the plan, as the demo does. Write the distinct count check into your change notes, so the next person knows why the column is unique. Run the cleanup script when you finish.

USE master;
GO
DROP DATABASE IF EXISTS UniqueIndexDemo;

A unique index is not a faster index, it is a stronger promise.

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