0

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.

3 Answers 3

1

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.

-1

A primary key is a logical concept. When you create a primary key you are telling the DB that every row will be unique based on the columns specified in the primary key. How the DB enforces this uniqueness is up to the DB vendor.

Microsoft has chosen to enforce the uniqueness of a primary key by creating a unique index.

-2
  1. When you create Index or PK, SQL never creates a "File".
  2. When you create PK, it will be clustered by default, unless you already have clustered index or specified PK as NON CLUSTERED.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.