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
REGISTER