Sql Server 2005 create index include

Last Post 05 Jun 2008 08:38 PM by jdunleavvertex. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jdunleavvertex
New Member
New Member

--
05 Jun 2008 12:14 PM
I am using Database Tuning Advisor in Sql Server 2005:
DTA is recommeneding a nonclustered index with an include on a column. (The index is not unique)

Since the index is not a unique index what is the benefit of using the include clause? Why not just put the include column in the composite index?

(ie)
INDEX OPTION 1
CREATE NONCLUSTERED INDEX JDI1 ON [dbo].[LineItem]
(
[lineItemId] ASC,
[dummycol] ASC,
[sourceId] ASC
)
INCLUDE ( [dummycol2]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]


INDEX OPTION 2
CREATE NONCLUSTERED INDEX JDI1 ON [dbo].[LineItem]
(
[lineItemId] ASC,
[dummycol] ASC,
[sourceId] ASC
[dummycol2] ASC
)
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
jdunleavvertex
New Member
New Member

--
05 Jun 2008 08:38 PM
After reading your comment here, and rereading the documentation for the include functionality that makes sense.
That's a bummer it doesn't exist in 2000, as the include seems pretty sweet.

Thanks!
You are not authorized to post a reply.

Acceptable Use Policy