Non clustered index including all columns smaller than the clustered index

Question

While messing around in SQL Server to get a better grasp on indices, I'm quite surprised to see that if I create an index (on another column than the PK) that includes all columns, it is still lighter than the clustered index.

Here for example, the clustered index is PK_Lecon and the non clustered one IX_Lecon_AllInclude.

The PK uses 7616 KB while the IX uses 4136 KB.
I used the "Disk Usage By Partition" report on SSMS.

What does SQL Server stores additionally in the clustered index?

---

The table definition:

CREATE TABLE Lecon (
    LEC_Numero INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Lecon PRIMARY KEY,
    LEC_STA_Numero INT NULL,
    LEC_Nom VARCHAR(50) NULL,
    LEC_INT_Site INT NOT NULL CONSTRAINT DF_Lecon_LEC_INT_Site DEFAULT 2,
    LEC_VAL_Numero INT NOT NULL CONSTRAINT DF_Lecon_LEC_VAL_Numero DEFAULT 1,
    LEC_PrixParticipant FLOAT NULL CONSTRAINT DF_Lecon_LEC_PrixParticipant DEFAULT 0,
    LEC_Status INT NOT NULL CONSTRAINT DF_Lecon_LEC_Status DEFAULT 0,
    LEC_Dispenser VARCHAR(20) NOT NULL CONSTRAINT DF_Lecon_LEC_Dispenser DEFAULT 'Interne',
    LEC_FCCL_ClasseID INT NULL,
    LEC_Parent_Numero INT NULL,
    LEC_CH_Numero INT NULL,
    CONSTRAINT FK_Lecon_CadreHoraire FOREIGN KEY (LEC_CH_Numero) REFERENCES CadreHoraire (CH_Numero),
    CONSTRAINT FK_Lecon_Intervenant FOREIGN KEY (LEC_INT_Site) REFERENCES Intervenant (INT_Numero),
    CONSTRAINT FK_Lecon_Parent_Lecon FOREIGN KEY (LEC_Parent_Numero) REFERENCES Lecon (LEC_Numero),
    CONSTRAINT FK_Lecon_Stage FOREIGN KEY (LEC_STA_Numero) REFERENCES Stage (STA_Numero),
    CONSTRAINT FK_Lecon_Validite FOREIGN KEY (LEC_VAL_Numero) REFERENCES Validite (VAL_Numero)
)

And the index one:

CREATE INDEX IX_Lecon_AllInclude ON Lecon (LEC_INT_Site ASC) INCLUDE (
    LEC_Numero,
    LEC_STA_Numero,
    LEC_Nom,
    LEC_VAL_Numero,
    LEC_PrixParticipant,
    LEC_Status,
    LEC_Dispenser,
    LEC_FCCL_ClasseID,
    LEC_Parent_Numero,
    LEC_CH_Numero
)

Answer 1

A clustered index is the table. That means every row of the table is physically stored in the clustered index structure (a B-tree) and the clustered index key columns are stored in the B-tree nodes (root -> intermediate -> leaf); ehe leaf level of the clustered index holds all the data columns — that’s the table’s data. So, the clustered index = data + index structure overhead.

A nonclustered index is a separate B-tree with its own key columns, Optional included columns (only stored at the leaf level), plus, a row locator pointing back to the base table. That locator is the clustered index key (if the table is clustered) or the RID (Row ID) (if the table is a heap).

Essentially in the clustered index, every column is stored in every page at the leaf level, with full row structure overhead. In the nonclustered index, the internal pages contain only the key (SomeColumn) — all other columns are just payload at the leaf, stored more compactly.

So even though you’ve included everything, SQL Server stores it in a denser, more index-friendly layout.

You can evetually peek inside the pages to see the differences.

DBCC TRACEON(3604);
GO

-- Get the first page of the clustered index
SELECT allocated_page_page_id AS PageID
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.Lecon'), 1, NULL, 'DETAILED')
WHERE page_type_desc = 'INDEX_PAGE' OR page_type_desc = 'DATA_PAGE';
GO

-- Pick one PageID from above and inspect it
DBCC PAGE (db_name, 1, <PageID>, 3);
GO

Then do the same for the nonclustered index:

-- NONCLUSTERED index 'IX_Lecon_AllInclude'
DECLARE @indexId INT;

SELECT @indexId = i.index_id
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('dbo.Lecon')
  AND i.name = 'IX_Lecon_AllInclude';

SELECT 
    index_id,
    page_type_desc,
    allocated_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.Lecon'), @indexId, NULL, 'DETAILED')
WHERE page_type_desc IN ('INDEX_PAGE', 'DATA_PAGE');
    
    DBCC PAGE (db_name, 1, <PageID>, 3);
    GO

https://learn.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-nonclustered-index?view=sql-server-ver17

https://learn.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-clustered-index?view=sql-server-ver17