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

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

 

Previous Post
How to Script Out Indexes from SQL Server
Next Post
Submit a “Dear DBA” Question

Related Posts

1 Comment. Leave new

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu