I certainly hadn’t planned on creating a part two of my post on clustered index columns but in the comments Jeremy Hughes pointed out that my understanding was if not incorrect, incomplete. So I did some additional testing, confirmed a few things with Paul Randal (b/t) and finally felt like I understood things better. It is in the interest of getting it right that I decided to continue this post.

So where was my understanding flawed? It centers around unique indexes. In a non-unique index the clustered index columns are included in the tree of the index in order to act as a uniquifier. In a unique index this is not necessary. However even in a unique index the clustered index columns will be included in the leaf level of the index.

Prove it you say?

Well of course!

Slightly modified version of the set up code from the previous post.

-- Create a convinent composite table 
SELECT Pers.BusinessEntityID, Addr.AddressID, 
Pers.Title, Pers.FirstName, Pers.MiddleName, Pers.LastName,  
Addr.AddressLine1, Addr.AddressLine2, Addr.City, 
    Addr.StateProvinceID, Addr.PostalCode, Addr.SpatialLocation
    INTO People1
FROM AdventureWorks2014.Person.Address Addr
JOIN AdventureWorks2014.Person.BusinessEntityAddress BEA
    ON Addr.AddressID = BEA.AddressID
JOIN AdventureWorks2014.Person.Person Pers
    ON BEA.BusinessEntityID = Pers.BusinessEntityID
GO
-- Add indexes including a non-unique clustered index and two duplicate indexes one unique and one non-unique.
CREATE CLUSTERED INDEX ix1_People1 ON People1(AddressLine1, AddressLine2, City, StateProvinceID, PostalCode)
GO
CREATE UNIQUE INDEX ix2_People1 ON People1(BusinessEntityID, AddressID)
GO
CREATE INDEX ix3_People1 ON People1(BusinessEntityID, AddressID)
GO

First we get the page information for the unique index.

-- Get the page id for ix2_People1
-- Info on sys.dm_db_database_page_allocations: 
--      http://www.jasonstrate.com/2013/04/a-replacement-for-dbcc-ind-in-sql-server-2012/
SELECT indexes.name, indexes.index_id, indexes.type_desc, 
    pages.allocated_page_file_id, pages.allocated_page_page_id, pages.is_iam_page
FROM sys.indexes
JOIN sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('People1'), NULL, NULL, NULL) pages
    ON indexes.object_id = pages.object_id
    AND indexes.index_id = pages.index_id
WHERE indexes.name = 'ix2_People1'

Next we look at the contents of one of the pages for the unique index. Again I’m grabbing the second page to avoid the IAM page. And lastly we pull the contents of the page.

DBCC TRACEON (3604);
-- Take a look at the contents of one of the index pages
DECLARE @DBID int
SET @DBID = DB_ID()
DBCC PAGE(@DBID, 1, 25736, 3)

I’m not an expert at reading the text output of DBCC PAGE so we are looking at the grid output. If you look at the column headings you can see that the index page does in fact contain both the columns from the unique index and the columns from the clustered index. What’s different is the string (key) at the end of the columns from the unique index. This means that these columns are actually part of the index tree while the other columns are only in the leaf level of the index.

This means that for a non-unique index all of the columns should have the (key) indicator. So let’s confirm by looking at the identical non-unique index we created earlier.

-- Get the page id for ix3_People1
-- Info on sys.dm_db_database_page_allocations: 
--      http://www.jasonstrate.com/2013/04/a-replacement-for-dbcc-ind-in-sql-server-2012/
SELECT indexes.name, indexes.index_id, indexes.type_desc, 
    pages.allocated_page_file_id, pages.allocated_page_page_id, pages.is_iam_page
FROM sys.indexes
JOIN sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('People1'), NULL, NULL, NULL) pages
    ON indexes.object_id = pages.object_id
    AND indexes.index_id = pages.index_id
WHERE indexes.name = 'ix3_People1'

DBCC TRACEON (3604);
-- Take a look at the contents of one of the index pages
DECLARE @DBID int
SET @DBID = DB_ID()
DBCC PAGE(@DBID, 1, 25992, 3)

And all of the columns have (Key) just as expected.

So the conclusion is that yes, all of the clustered index columns will be in all non-clustered indexes. However if that non-clustered index is unique then the clustered index columns will only be in the leaf level of the index not the tree.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication Tagged: index, language sql, microsoft sql server