Update to “The clustered index columns are in all of the non clustered indexes.”

5

October 20, 2014 by Kenneth Fisher

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'

CI_NCI_Pt2_1

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)

CI_NCI_Pt2_2

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'

CI_NCI_Pt2_3

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)

CI_NCI_Pt2_4

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.

5 thoughts on “Update to “The clustered index columns are in all of the non clustered indexes.”

  1. Hi Kenneth, I always love your posts and although I subscribe to a number of SQL sites, I give yours priority because the articles are concise, to the point and written in a way that us ordinary folk can relate to.

    Appreciate your article which has also clarified in my mind the non-clustered index structure in a way that the final piece of the puzzle has fallen into place for me. Over and above that, also appreciate your humble response in the interests of “getting it right.”

  2. […] Date columns are larger causing all of the indexes to be larger. […]

  3. […] This is because the clustered index columns are in each non clustered index. (Read here and here.) Since the rebuild doesn’t require changing the clustered index this doesn’t apply. Of […]

  4. […] A while back Steve Stedman (b/t) started the database corruption challange. In the very first one Brent Ozar (b/t) used a rather neat trick to recover the data. It’s based on the fact that a non-clustered index contains some of the information from the table. Specifically any columns that are indexed, included or in the clustered index (if there is one). (Clustered index columns are included in the non-clustered indexes Part 1, Part 2) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: