I came across a question on Stack Overflow in which an individual asked what using
INCLUDE with your
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
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.
SELECT id1, id2, MAX(desc1), COUNT(id1), MAX(col1) FROM table1
WHERE date1 >= DATEADD(mm, -6, GETDATE())
GROUP BY id1, id2
CREATE INDEX [index1]
ON [table1] ([date1], [id1], [id2])
CREATE INDEX [index2]
ON [table1] ([date1])
INCLUDE ([id1], [id2])
|Actual number of rows
|Size on disk (kb)
|Total execution time (ms)
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