Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Benefits of INCLUDE columns in a non-clustered index

Posted on 2010-11-11
4
Medium Priority
?
875 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 664 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:Brendt Hess
Brendt Hess earned 668 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 70

Accepted Solution

by:
Scott Pletcher earned 668 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

876 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