Uniquifier: The Hidden Cost of Duplicate Clustered Keys

The uniquifier is a hidden column that SQL Server adds to duplicate rows in a non-unique clustered index. You never see it in a SELECT or in the table design. It still takes space in every row that repeats a key. Let’s measure it row by row and look at it on the page.

Why SQL Server Needs It

A clustered index sorts the table rows by its key. Every nonclustered index stores that key too, so it can find the matching table row. This only works when each key points to exactly one row.

You can still create a clustered index that allows duplicates. Then SQL Server must tell equal keys apart on its own. It adds a 4-byte integer, the uniquifier, to each repeated row. The first row of every key value gets none.

This is why a unique clustered key is the better default. Nonclustered indexes copy the clustered key, so any extra bytes in it repeat in every index. The tests below put numbers on that cost.

Build Two Tables

I ran everything here on SQL Server 2025. The first script creates a test database.

IF DB_ID(N'UniqDemo') IS NULL CREATE DATABASE UniqDemo;

Both tables hold 100,000 rows with an int key and a 20-character note. In CodesUnique every key is different. In CodesDup each key appears 100 times, so there are 1,000 keys. The note has a fixed length, so only the key can change the row size.

USE UniqDemo;
GO
DROP TABLE IF EXISTS dbo.CodesUnique;
DROP TABLE IF EXISTS dbo.CodesDup;
CREATE TABLE dbo.CodesUnique (Code int NOT NULL, Note char(20) NOT NULL);
CREATE UNIQUE CLUSTERED INDEX cx_CodesUnique ON dbo.CodesUnique (Code);
CREATE TABLE dbo.CodesDup (Code int NOT NULL, Note char(20) NOT NULL);
CREATE CLUSTERED INDEX cx_CodesDup ON dbo.CodesDup (Code);
INSERT INTO dbo.CodesUnique (Code, Note)
SELECT value, 'tea' FROM GENERATE_SERIES(1, 100000);
INSERT INTO dbo.CodesDup (Code, Note)
SELECT (value - 1) / 100 + 1, 'tea' FROM GENERATE_SERIES(1, 100000);

Can you select the hidden column? Try it.

SELECT UNIQUIFIER FROM dbo.CodesDup;

Msg 207, Level 16, State 1, Line 1
Invalid column name 'UNIQUIFIER'.

SQL Server refuses. The column has no name you can use in a query, so we need other tools.

Measure the Rows

The function sys.dm_db_index_physical_stats reports record sizes for every level of an index. Level 0 is the leaf, where the table rows live. DETAILED mode reads every page, so use it on a test copy.

SELECT OBJECT_NAME(object_id) AS TableName, index_level AS TreeLevel, page_count AS Pages,
       record_count AS Records, min_record_size_in_bytes AS MinBytes,
       max_record_size_in_bytes AS MaxBytes, CAST(avg_record_size_in_bytes AS decimal(5,2)) AS AvgBytes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE object_id IN (OBJECT_ID(N'dbo.CodesUnique'), OBJECT_ID(N'dbo.CodesDup')) AND index_id = 1
ORDER BY TableName DESC, TreeLevel;

Every row of CodesUnique takes 31 bytes. In CodesDup the smallest row is 31 bytes and the largest is 39. The table grew from 409 to 508 pages.

The extra is 8 bytes, not 4. The uniquifier needs 4 bytes. The row also needs a variable-length part, which adds a 2-byte count and a 2-byte offset. The 1,000 first rows stay at 31 bytes, and the other 99,000 pay the 8. That gives the average of 38.92.

The tree grew as well. CodesUnique has two levels, and CodesDup has three. The upper rows hold the uniquifier too, so they went from 11 to 19 bytes.

See It on the Page

Record sizes show the cost. The page shows the value itself. This small table has three rows with key 10, two with key 20 and one with key 30.

DROP TABLE IF EXISTS dbo.SmallDup;
CREATE TABLE dbo.SmallDup (Code int NOT NULL, Note char(20) NOT NULL);
CREATE CLUSTERED INDEX cx_SmallDup ON dbo.SmallDup (Code);
INSERT INTO dbo.SmallDup (Code, Note)
VALUES (10, 'tea'), (10, 'tea'), (10, 'tea'), (20, 'tea'), (20, 'tea'), (30, 'tea');

The command DBCC PAGE prints a page. It normally needs trace flag 3604 to print on your screen. DBCC TRACEON (3604) sets that flag for your session only. The option WITH TABLERESULTS returns rows instead, so we need no flag. The script below finds the data page, reads it into a temp table and lists the uniquifier of each slot. A slot is the place of one row on the page.

DROP TABLE IF EXISTS #Page;
CREATE TABLE #Page (RowNo int IDENTITY(1,1), ParentObject varchar(255), Object varchar(255), Field varchar(255), Value varchar(255));
DECLARE @cmd nvarchar(200);
SELECT @cmd = N'DBCC PAGE (N''UniqDemo'', ' + CAST(allocated_page_file_id AS nvarchar(10)) + N', '
            + CAST(allocated_page_page_id AS nvarchar(10)) + N', 3) WITH TABLERESULTS'
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.SmallDup'), 1, NULL, 'DETAILED')
WHERE page_type_desc = N'DATA_PAGE';
INSERT INTO #Page (ParentObject, Object, Field, Value) EXEC (@cmd);
SELECT ParentObject AS Slot, Value AS Uniquifier FROM #Page WHERE Field = N'UNIQUIFIER' ORDER BY RowNo;

Slot 0 is the first row with key 10. Its uniquifier is 0, and it takes 31 bytes. The next two rows with key 10 carry 1 and 2 and take 39 bytes each. Key 20 starts again at 0. Key 30 has one row, so it stays at 31 bytes. The count restarts for each key, so the uniquifier only has to be unique inside one key value.

The Cost in a Nonclustered Index

Every nonclustered index row stores the clustered key to find its table row. It copies the uniquifier as well. Let’s add an index on Note to both tables and measure the leaf level.

CREATE INDEX ix_Note ON dbo.CodesUnique (Note);
CREATE INDEX ix_Note ON dbo.CodesDup (Note);
SELECT OBJECT_NAME(object_id) AS TableName, page_count AS Pages, min_record_size_in_bytes AS MinBytes,
       max_record_size_in_bytes AS MaxBytes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE object_id IN (OBJECT_ID(N'dbo.CodesUnique'), OBJECT_ID(N'dbo.CodesDup')) AND index_id = 2 AND index_level = 0
ORDER BY TableName DESC;

The same 8 bytes appear again. The index grew from 372 to 470 pages. With the table itself, that is 99 plus 98, or 197 more pages, about 1.5 MB for 100,000 rows.

When the Table Has a Text Column

Many tables have a varchar column. It already brings a variable-length part, so the price changes. Two new tables show how.

DROP TABLE IF EXISTS dbo.VarUnique;
DROP TABLE IF EXISTS dbo.VarDup;
CREATE TABLE dbo.VarUnique (Code int NOT NULL, Note varchar(20) NOT NULL);
CREATE UNIQUE CLUSTERED INDEX cx_VarUnique ON dbo.VarUnique (Code);
CREATE TABLE dbo.VarDup (Code int NOT NULL, Note varchar(20) NOT NULL);
CREATE CLUSTERED INDEX cx_VarDup ON dbo.VarDup (Code);
INSERT INTO dbo.VarUnique (Code, Note)
SELECT value, 'tea' FROM GENERATE_SERIES(1, 100000);
INSERT INTO dbo.VarDup (Code, Note)
SELECT (value - 1) / 100 + 1, 'tea' FROM GENERATE_SERIES(1, 100000);
SELECT OBJECT_NAME(object_id) AS TableName, page_count AS Pages, min_record_size_in_bytes AS MinBytes,
       max_record_size_in_bytes AS MaxBytes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE object_id IN (OBJECT_ID(N'dbo.VarUnique'), OBJECT_ID(N'dbo.VarDup')) AND index_id = 1 AND index_level = 0
ORDER BY TableName DESC;

The unique rows take 18 bytes. In the duplicate table the smallest row is 20 bytes and the largest is 24. The hidden column counts as variable length. So every row gets a slot for it, even the first row of a key. That slot costs 2 bytes. A row with a real uniquifier adds 4 bytes more.

Is 8 Bytes Worth Worrying About?

You could say 8 bytes is nothing. Fair point. For a table of a few thousand rows it is. But the cost grows with the row count, and every nonclustered index repeats it. More pages also mean more reads for each scan and more memory for each cached table.

Make the Key Unique Yourself

The cheapest fix is a key that is already unique, such as an identity column. The second fix is to add a tie-breaker column to the key and declare the index unique. The next table does this with a RowNo column.

DROP TABLE IF EXISTS dbo.CodesKeyed;
CREATE TABLE dbo.CodesKeyed (Code int NOT NULL, RowNo int NOT NULL, Note char(20) NOT NULL);
CREATE UNIQUE CLUSTERED INDEX cx_CodesKeyed ON dbo.CodesKeyed (Code, RowNo);
INSERT INTO dbo.CodesKeyed (Code, RowNo, Note)
SELECT (value - 1) / 100 + 1, value, 'tea' FROM GENERATE_SERIES(1, 100000);
SELECT OBJECT_NAME(object_id) AS TableName, page_count AS Pages, min_record_size_in_bytes AS MinBytes,
       max_record_size_in_bytes AS MaxBytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.CodesKeyed'), 1, NULL, 'DETAILED')
WHERE index_level = 0;

Each row takes 35 bytes, 4 more than the unique table, because of the new column. Still, the table needs 459 pages against 508 for CodesDup. A visible 4-byte column costs less than a hidden 4-byte value with 4 bytes of overhead. Add a tie-breaker only when it has a meaning in your data.

Find Them on Your Server

This query lists every user table whose clustered index is not unique. They are the candidates.

SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName
FROM sys.indexes
WHERE type = 1 AND is_unique = 0 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
ORDER BY TableName;

The list shows candidates only. If no key value repeats, every row is a first row and no uniquifier is stored. Count the duplicates per key with GROUP BY before you change anything.

A Short Checklist

• Cluster on a narrow, unique key when you can, such as an identity column.
• Declare the index UNIQUE when the data is unique.
• Count the duplicates per key before you pick a non-unique clustered key.
• Remember that every nonclustered index copies the cost.
• Read the record sizes on a test copy, not on a busy server.

Clean Up

USE master;
GO
ALTER DATABASE UniqDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE UniqDemo;

The uniquifier is not a bug, it is a bill that comes with every duplicate.

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