Monday, September 27, 2010

SQL Server never stores the same column twice

Consider the following table.

     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 */
        @col2 INT

SET @col1 = 1
SET @col2 = 10000

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

      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 traceon(3604)
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.

1 comment:

Neil said...

Thanks for proving this. It was something i always wondered how SQL handled it.