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
