0

Indexes with Included Columns on SQL Server

I came across a question on Stack Overflow in which an individual asked what using INCLUDE with your INDEX does.

The basic principle is to index columns that are being filtered, for which in the example below would be PostalCode. The other columns that are being selected are ones that should be included (AddressLine1, AddressLine2, City, StateProvinceID). These columns are added to the leaf level and can therefore cover more queries.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

Even though I have adapted the use of INCLUDE, I ran a short test to see if there would be any performance difference.

The Query
SELECT id1, id2, MAX(desc1), COUNT(id1), MAX(col1) FROM table1
WITH (INDEX(index1))
WHERE date1 >= DATEADD(mm, -6, GETDATE())
GROUP BY id1, id2
Index 1
CREATE INDEX [index1]
ON [table1] ([date1], [id1], [id2])
Index 2
CREATE INDEX [index2]
ON [table1] ([date1])
INCLUDE ([id1], [id2])
Results
  Index 1 Index 2
Actual number of rows 22959357 22959357
Size on disk (kb) 6229736 6215496
Total execution time (ms) 629161 624089

The table was about 28 GB and both indexes added roughly 6 GB (20% of table size). Additionally, both indexes scanned roughly 70% of the table, and execution time between using INCLUDE and not using it were negligible.

References: Index with Included Columns

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published. Required fields are marked *