Consider the following table.
CREATE TABLE dbo.tbl
col1_pky INT PRIMARY KEY,
CREATE INDEX nc_col2_col1
ON dbo.tbl(col2_nc_index, col1_pky)
I have created a table dbo.tbl with the column col1_pky as primary key.
A composite Non Clustered index is created on col2_nc_index,col1_pky columns.As by definition every non clustered index contains the clustered index key.
So going by definition, the non-clustered index should contain the following
* col2_nc_index,col1_pky - Index definition
* col1_pky - Clustered Index key
col1_pky is supposed to repeat twice as its a part of the nonclustered index and also clustered index key. But, SQL Server avoids the same by not storing the same
/* Data Generation Script */
DECLARE @col1 INT,
SET @col1 = 1
SET @col2 = 10000
WHILE @col1 < 10000
INSERT INTO tbl
SET @col1 = @col1 + 1
SET @col2 = @col2 - 1
As usual let us use DBCC IND / PAGE to check the same.
To check the root page
DBCC ind ( 'dbadb', 'tbl', -1)
Root page is found as 18684
Let us take a look at the contents of root page using DBCC PAGE
DBCC page(dbadb, 1, 18684, 3)
Note that col1_pky appears only once and doesnt appear twice. To confirm the same let us check the contents of a leaf level page.
DBCC page(dbadb, 1, 19044, 3)
Again col1_pky is present only once. So, SQL Server always stores a column only once in a table.