In the last blog post I have talked about unique/non-unique clustered indexes on a heap

table. A table without a clustered index is called a heap table in SQL Server.

When you define a clustered index on such a table, the table data gets structured

and is therefore referred as clustered table. In this blog post I

want to talk about the differences in unique and non-unique clustered indexes, and

what are the storage impacts between those 2 types of clustered indexes.

As a prerequisite I assume that you have a basic understanding of clustered indexes,

and that you know the difference between heap and clustered tables, and how your data

pages are structured when a clustered index is defined on a table.

Let's start by looking on a unique clustered index. With SQL Server you have several

possibilities to define a unique clustered index. The first way – the easy one – is

to define a PRIMARY KEY constraint on a column. SQL Server enforces

this PRIMARY KEY constraint through the creation of a unique clustered

index on that table and that column. The another option is to create a unique clustered

index through the CREATE CLUSTERED INDEX statement – but when you

don't specify the UNIQUE property, SQL Server will create a non-unique

clustered index by default for you! The following code fragment creates the Customers table

that you already know from the previous blog posting, but this time we create a PRIMARY

KEY constraint on the column CustomerID. Therefore SQL Server

creates a unique clustered index on that table and sorts the data pages in the leaf

level according the values in the column CustomerID.

--

Create a table with 393 length + 7 bytes overhead = 400 bytes

-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24

CREATE TABLE Customers

(

CustomerID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),

CustomerName CHAR(100) NOT NULL,

CustomerAddress CHAR(100) NOT NULL,

Comments CHAR(189) NOT NULL

)

GO

-- Insert 80.000 records

DECLARE @i INT = 1

WHILE (@i <= 80000)

BEGIN

INSERT INTO Customers VALUES

(

'CustomerName' + CAST(@i AS CHAR),

'CustomerAddress' + CAST(@i AS CHAR),

'Comments' + CAST(@i AS CHAR)

)

SET @i += 1

END

GO

After we have identified the index root page (through the use of the DBCC

IND command), we can dump out that page with the DBCC PAGE command.

In my case the index root page is 775:

DBCC PAGE(UniqueClusteredIndexStructure, 1, 775, 3)

GO

As you can see from the following figure each index record contains the clustered

key, in this case the value of the column CustomerID.

When you examine the byte by byte representation of a clustered index record, you

can see that SQL Server uses here the following bytes:

• 1 byte: Status Bits

• n bytes: Clustered Key – in this case 4 bytes

• 4 bytes: PageID

• 2 bytes: FileID

As you can see the length of the clustered key has a direct relationship of the length

of an index record. This mean as smaller your clustered key is, the more index record

can be put onto an index page, and therefore your clustered index will be much more

compact and will perform faster and are easier to maintain. When you walk down your

clustered index you will see that all intermediate levels have the same storage format

as described above. There are no differences on each level, expect the index leaf

level, because this level contains your actual logically ordered data pages.

Let's have now a look onto non-unique clustered indexes in SQL Server and how they

differ from unique clustered indexes. To demonstrate this kind of indexes, I have

just recreated the Customers table and created a non-unique clustered

index on that table through the CREATE CLUSTERED INDEX statement:

--

Create a table with 393 length + 7 bytes overhead = 400 bytes

-- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24

CREATE TABLE Customers

(

CustomerID INT NOT NULL,

CustomerName CHAR(100) NOT NULL,

CustomerAddress CHAR(100) NOT NULL,

Comments CHAR(181) NOT NULL

)

GO

-- Create a non unique clustered index

CREATE CLUSTERED INDEX idx_Customers_CustomerID

ON Customers(CustomerID)

GO

Finally I have inserted 80.000 records, where the column CustomerID (the

clustered key) is not unique anymore:

--

Insert 80.000 records

DECLARE @i INT = 1

WHILE (@i <= 20000)

BEGIN

INSERT INTO Customers VALUES

(

@i,

'CustomerName' + CAST(@i AS CHAR),

'CustomerAddress' + CAST(@i AS CHAR),

'Comments' + CAST(@i AS CHAR)

)

INSERT INTO Customers VALUES

(

@i,

'CustomerName' + CAST(@i AS CHAR),

'CustomerAddress' + CAST(@i AS CHAR),

'Comments' + CAST(@i AS CHAR)

)

INSERT INTO Customers VALUES

(

@i,

'CustomerName' + CAST(@i AS CHAR),

'CustomerAddress' + CAST(@i AS CHAR),

'Comments' + CAST(@i AS CHAR)

)

INSERT INTO Customers VALUES

(

@i,

'CustomerName' + CAST(@i AS CHAR),

'CustomerAddress' + CAST(@i AS CHAR),

'Comments' + CAST(@i AS CHAR)

)

>

SET @i += 1

END

GO

When you now dump out the root index page of the non-unique clustered

index, you get the following result:

As you can see, SQL Server returns here an additional column named UNIQUIFIER

(key). This column is used by SQL Server to make a non-unique clustered key

unique. Behind the scenes it is a 4 byte long integer value starting at 0. E.g. when

you have 2 customers with the ID 1380 the first record gets the uniquifier value 0

and the second one gets the uniquifier value of 1. But SQL Server only stores the

uniquifier in the navigation structure of an index (all levels above the leaf level),

when the uniquifier is not equal to 0. SQL Server only includes uniquifier values

of 0 in the navigation structure of a non-unique clustered index, which means that

the navigation structure will never store the uniquifier physically. The only place

where the uniquifier is stored in a non-unique clustered index is on the data pages,

where the actual data records are stored. The following figure shows a data page dump

of our clustered index, where you can also see the stored uniquifier.

So the only difference between a unique and non-unique clustered index is on the data

pages, because when using a non-unique clustered index, SQL Server will use the 4

byte long uniquifier to make them unique, which is a small storage overhead that you

have to keep in mind, when working with non-unique clustered indexes. You can download

the T-SQL script for this posting here.

In the next posting we will work out the differences between unique/non-unique non-clustered

indexes defined on unique clustered indexes. Stay tuned 🙂

-Klaus