Solved

Benefits of INCLUDE columns in a non-clustered index

Posted on 2010-11-11
4
828 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:
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now