SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.


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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...