Indexes and Included Columns

In my previous blog post, we saw how using INCLUDE to add a column to an index can make a difference compared to a key column. Let’s do a quick overview of INCLUDE and when it should be used.

Included columns are columns that can added to an index as non-key columns. They are only added to the leaf nodes of an index and have a bit more flexibility. Having trouble adding a particular data type to an index? Included columns can be data types unable to be added as key columns. Are you possibly maxed out on index key columns? Use INCLUDE to add any necessary columns.

Are you seeing key lookups in your execution plan? Including a column or two may be what you need to improve performance. Let’s look at an example of using INCLUDE to create a “covering index” to cover a query.

Left Out in the Cold

We’ll use the StackOverflow2013 database and run this query:

SELECT PostId, CreationDate
FROM Votes
WHERE VoteTypeId = 5
AND CreationDate > '2012-12-01'
AND CreationDate < '2013-01-01';
GO

Batteries Not Included

Our plan shows one big clustered index scan to gather our data. You might look at VoteTypeId and CreationDate in the WHERE clause and create an index based on those columns:

CREATE INDEX IX_VoteTypeId_CreationDate ON [dbo].[Votes] (
	[VoteTypeId]
	,[CreationDate]
	);
GO

If we re-run our query:

Better != Good

Our query didn’t take as long to run and our new index was used for our query…mostly. There was an index seek for VoteTypeId and CreationDate but a key lookup was needed to the PK_Votes_Id clustered index in order to retrieve the PostId. We don’t need to add PostId as an indexed key column since it’s only being used in the SELECT statement. Let’s add another index with PostId included:

CREATE INDEX IX_VoteTypeId_CreationDate_Include_PostId ON [dbo].[Votes] (
	[VoteTypeId]
	,[CreationDate]
	) INCLUDE ([PostId]);
GO

One index seek and our query is covered.

Feel the Need

Just like any other index, watch what columns you’re adding and whether you’re adding columns that are going to do more good than harm. Don’t waste space with a large index that slows down processing with the need to stay up-to-date with data changes.

Thanks for reading!