SQL Server never stores the same column twice

Consider the following table.

CREATE TABLE dbo.tbl
  (
     col1_pky      INT PRIMARY KEY,
     col2_nc_index INT
  ) 


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
column twice.


/* Data Generation Script */
DECLARE @col1 INT,
        @col2 INT

SET @col1 = 1
SET @col2 = 10000

WHILE @col1 < 10000
  BEGIN
      INSERT INTO tbl
      SELECT @col1,
             @col2

      SET @col1 = @col1 + 1
      SET @col2 = @col2 - 1
  END 


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 traceon(3604)
GO
DBCC page(dbadb, 1, 18684, 3)
GO 




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)
GO 




Again col1_pky is present only once. So, SQL Server always stores a column only once in a table.