I thought I had said almost all that could be said about nonclustered index keys in a post made almost exactly two years ago, on March 16, 2008. But there's more.
To get all the benefit from today's post, you'll really have to read that one, but I'll synthesize the crucial details here.
Every index needs to be unique, in some way or another, so that every index row can be referenced uniquely. A clustered index can be declared unique, which it might be if you built an index on EmployeeID_number. If you build the index on a non-unique column, such as LastName, SQL Server will uniquify it for you, by adding an extra hidden column as a second index key.
Nonclustered indexes also need to be unique. In addition, each nonclustered index has to have a way of 'pointing to' the row in the table it references, and if the table has a clustered index, this pointer is a copy of the clustered index key(s). Since the clustered key(s) must be unique, as stated in the previous paragraph, nonclustered indexes are automatically made unique when you add the clustered key. My above referenced post refers to the fact that if you if you explicitly add the clustered key column(s) to the nonclustered index definition, those columns will only be part of the nonclustered key if the nonclustered index was not declared unique. Otherwise, the clustered key column(s) will be treated like INCLUDE columns.
The definition of a KEY column is one that is propagated up through all levels of the index tree. (This is how SQL Server builds an index, by taking the key column(s) from the first row on every page at one level, and building a set of node pages to contain all those 'first row' values, with pointers to the pages those rows came from.) So, if a column is shows up in the intermediate, or node, index levels, it is a KEY column.
So the other post showed you that when the nonclustered index was unique, clustered key(s) are stored as INCLUDE columns, which means they just show up at the leaf level. They are not needed as part of the nonclustered key.
However, if the clustered index is unique and you have a nonunique nonclustered index, we get the opposite behavior. Even if we declare the clustered key column as an INCLUDE column, it is actually stored as a KEY column.
Here is the first part of the script I used in the other post, that copies a table from the AdventureWorks database. However, in this case I am building a unique clustered index:
USE testdb
GO
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'Sales')
DROP TABLE Sales;
GO
SELECT * INTO Sales
FROM AdventureWorks.Sales.SalesOrderDetail;
GO
Now build a clustered index on SalesOrderID and three similar nonclustered indexes on SalesOrderDetailID, which is unique.
CREATE UNIQUE CLUSTERED INDEX Sales_ID_Index ON Sales(SalesOrderDetailID);
GO
Next, build three similar nonclustered indexes:
-- The first index is on a single, nonunique column
CREATE INDEX SalesOrderID_Index1 ON Sales(SalesOrderID);
GO
-- The second index is composite and explicitly adds the clustered key column
CREATE INDEX SalesOrderID_Index2 ON Sales(SalesOrderID, SalesOrderDetailID);
GO
-- The third index adds the clustered index key as an INCLUDE column
CREATE INDEX Sales_DetailID_Index3 ON Sales(SalesOrderID);
INCLUDE (SalesOrderDetailID);
GO
Using the table I showed you in the previous post, I can capture the output of DBCC IND and find an upper level page from each nonclustered index.
TRUNCATE TABLE sp_index_info
INSERT INTO sp_index_info
EXEC ('DBCC IND ( testdb, Sales, -1)' );
GO
SELECT PageFID, PagePID, IndexID
FROM sp_index_info
WHERE IndexID > 1 AND IndexLevel > 0
AND PrevPagePID = 0
ORDER BY IndexID;
GO
Here are my results:
PageFID PagePID IndexID
------- ----------- -------
1 4056 2
1 904 3
1 1888 4
Now use DBCC PAGE to look at each of these pages
DBCC TRACEON (3604);
DBCC PAGE(testdb, 1, 4056, 3);
DBCC PAGE(testdb, 1, 904, 3);
DBCC PAGE(testdb, 1, 1888, 3);
You can see that all 3 indexes are identical, even at the non-leaf level. Of course, the Child Pages they point to are different, because they are different indexes on different pages. But the rows and columns on each page are the same.
Each nonclustered index has to store the clustered key. And since my clustered index is unique, and the nonclustered indexes aren't declared as unique, the clustered key MUST be part of the key of the nonclustered indexes. Even though the 3rd nonclustered tries to make the clustered key an INCLUDE column, since it is needed for the key, it is treated as a key. It will not be stored twice just to make is also an INCLUDE column.
So now I must have said all that could be said about nonclustered index keys, right?
Maybe not….
Have fun!
~Kalen