Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Benefits of INCLUDE columns in a non-clustered index

Posted on 2010-11-11
4
Medium Priority
?
851 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 69

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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