Solved

Benefits of INCLUDE columns in a non-clustered index

Posted on 2010-11-11
4
841 Views
Last Modified: 2012-05-10
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])
0
Comment
Question by:purplesoup
4 Comments
 
LVL 5

Assisted Solution

by:jcott28
jcott28 earned 166 total points
ID: 34114390
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
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 167 total points
ID: 34114436
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 34114779
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
 

Author Closing Comment

by:purplesoup
ID: 34137355
Thanks for your help
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question