Benefits of INCLUDE columns in a non-clustered index

Can anyone explain the benefit of the INCLUDE column in the non-clustered indexes below?

I've been reading about include columns here

http://msdn.microsoft.com/en-us/library/ms190806.aspx

and it seems the benefit is to avoid index size limits and to include datatypes that are not allowed as index key columns, neither of which appears to apply here.

I've tried running these sorts of queries

SELECT Datefield FROM DateList WHERE Year>2000 AND Year<2030 AND Month>9
SELECT Datefield FROM DateList WHERE Year=2000 AND Month>5
SELECT Datefield FROM DateList WHERE DayOfMonth>28

displaying the extimated execution plan and it seems that it likes any columns being SELECTED added to an index as an INCLUDE column - which would be an explanation as to why Datafield would be included in the indexes below, but can anyone confirm this why they should be included?

CREATE TABLE [dbo].[DateList](
      [Datefield] [date] NOT NULL,
      [Holiday] [bit] NOT NULL,
      [PayrollWeek] [smallint] NOT NULL,
      [DayOfMonth]  [smallint] NOT NULL,
      [Year]  [smallint] NOT NULL,
      [Month]  [smallint] NOT NULL,
      [LastDayOfMonth] [bit] NOT NULL,
      [WeekDay] [bit] NOT NULL,
      [PayrollStartDate] [date] NULL
)
GO
CREATE UNIQUE CLUSTERED INDEX [IX_DateList_DateField] ON [dbo].[DateList]
(
      [Datefield] ASC
)
CREATE NONCLUSTERED INDEX [IX_DateList_DayOfMonth] ON [dbo].[DateList]
(
      [DayOfMonth] ASC
)
INCLUDE ( [Datefield])

CREATE NONCLUSTERED INDEX [IX_DateList_MonthYear] ON [dbo].[DateList]
(
      [Year] ASC,
      [Month] ASC
)
INCLUDE ( [Datefield])
purplesoupAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Yes, the primary benefit is to create a covering index.

It might also allow an index to be used because of an additional column in the WHERE without forcing you put that column in the index key.

For example:

SELECT *
FROM ...
WHERE indexCol1 = ...
AND indexCol2 >= ...
AND includeCol1 LIKE 'abc%'

If "includeCol1" was not included in the index, SQL might have to scan the whole table.  If adding the included column allows SQL to use the index, and do RID/bookmark lookups to get back to all the columns in the SELECT, it could still be a big help to the query.
0
 
jcott28Connect With a Mentor Commented:
all non clustered indexes automatically include all the columns in the clustered index.  

For tables where there are more columns that your query, the query is better off with your non-clustered index that includes a lot of columns.  This is because it'll process less pages.
0
 
Brendt HessConnect With a Mentor Senior DBACommented:
The third advantage is to create a Covering Index without actually indexing the data that is not needed for the lookup.  For example, if you need to look up something by year and month, but need to return the full datefield, the index IX_DateList_MonthYear would be perfect.  You search the index based on year and month, and return the DateField data without looking up the record.  Especially when dealing with non-clustered indexes, this type of technique can increase query throughput significantly (over just the plain index without the INCLUDE statement), and can increase indexing performance (over an index where the included data is incorporated as an indexed field, instead of just an attached data element).

0
 
purplesoupAuthor Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.