Indexes: Do Included Columns Always Go on a Non-Root Page? (video)

on May 10, 2016

Recently, I was thinking about nonclustered indexes in SQL Server, and how included columns are stored.

Is SQL Server smart enough to optimize the storage for small indexes with includes? Find out in this free seven minute video.

Scripts Used in this Video

Want to run the demo yourself? This script works on SQL Server 2012 and higher.

Be aware that sys.dm_db_database_page_allocations is technically an undocumented procedure, and can be time consuming when run against large indexes. That means this script is suitable for test servers only.

USE SQLIndexWorkbook;
GO

CREATE TABLE dbo.pagetest (
    FirstName varchar(255), 
    FirstNameByBirthDateId int, 
    Gender Char(1)
);
GO

INSERT dbo.pagetest VALUES 
('Kale', 121, 'M'),
('Kris', 13, 'M'),
('Kris', 138981, 'F'),
('Leaf', 1001, 'M');
GO

CREATE INDEX nc_pagetest 
    on dbo.pagetest (FirstName, FirstNameByBirthDateId) 
    INCLUDE (Gender);
GO

SELECT 
    allocated_page_page_id, 
    page_level
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('pagetest'),NULL,NULL,'detailed')
where 
    is_allocated=1
    and index_id=2
    and page_type_desc <> 'IAM_PAGE'
GO

SET NOCOUNT ON;
GO
INSERT dbo.pagetest VALUES 
('Kale', 121, 'M'),
('Kris', 13, 'M'),
('Kris', 138981, 'F'),
('Leaf', 1001, 'M')
GO 100

SELECT 
    allocated_page_page_id, 
    page_level
from sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('pagetest'),NULL,NULL,'detailed')
where 
    is_allocated=1
    and index_id=2
    and page_type_desc <> 'IAM_PAGE'
GO

DROP TABLE dbo.pagetest;
GO