Sql-server – Clustered Index and PK – Two different Objects

clustered-indexprimary-keysql server

I have a table which has a Clustered PK as follows:

CREATE TABLE [dbo].[PanelCcmRawData] ADD  CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED 
(
    [CcmRawDataId] ASC,
    [LineageId] ASC
)

I understand that when you create a Clustered Index, the table itself is the clustered index. However, when you make the Clustered Index the PK as well, it seems SQL Server creates an actual index file. I noticed this because when I ran a query to see all the indexes on my table, the number of seeks on the index and the size etc., I saw an entry for PK_CcmRawData with a size of 331GB.

I was a bit surprised to see this. I'm curious why, when creating a Clustered Index that is also the PK, SQL Server creates an index file. I would have thought the table itself would act as the Clustered Index and PK. So there appears to be something I'm not understanding.

Best Answer

The leaf level of the Clustered index is the table. You still have the other levels of the B-Tree (root, intermediate) as a part of the clustered index which account for why there is an index for your PK on the table.

Related Question