Index with Included Column

Documents SQL Server 2005 and 2008 databases.

Moderators: JonathanWatts, David Atkinson, Michelle Taylor

Index with Included Column

Postby tharman » Fri Jan 14, 2011 12:55 am

When I generate a .chm file for our database, I've noticed that one of the indexes which has an included column, the included column is being shown as the 1st element of the key.

Is there a configuration option I've missed?
tharman
 
Posts: 3
Joined: Fri Jan 14, 2011 12:47 am

Postby james.billings » Mon Jan 17, 2011 2:48 pm

I don't think there are any options that would really affect this. Can I first get you to check you're using the latest patch in case it helps? You'll find that in this post - if that doesn't help could you maybe post / email to support a create script for the object in question so we can try to replicate the problem?
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby tharman » Mon Jan 17, 2011 11:49 pm

The "About SQL Doc" reports I'm on 2.0.1.51, so I think I'm up to date.

Here's a table definition that produces the strange results when you generate the .chm, and below is the Indexes section in the chm.

SERVERPROPERTY('productversion')
9.00.4053.00 SP3

Code: Select all
CREATE TABLE [dbo].[RepAccount]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Account_ID] [int] NOT NULL,
[Rep_ID] [int] NOT NULL,
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_RepAccount_DateModified] DEFAULT (getdate())
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RepAccount] ADD CONSTRAINT [PK_RepAccount] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RepAccount] ADD CONSTRAINT [IX_RepAccount_AccountID] UNIQUE NONCLUSTERED  ([Account_ID], [Rep_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_RepAccount_RepID] ON [dbo].[RepAccount] ([Rep_ID], [DateModified]) INCLUDE ([Account_ID]) ON [PRIMARY]
GO


Name Columns
PK_RepAccount ID
IX_RepAccount_AccountID Account_ID, Rep_Id
IX_RepAccount_RepID Account_ID, Rep_Id, DateModified
tharman
 
Posts: 3
Joined: Fri Jan 14, 2011 12:47 am

Postby james.billings » Tue Jan 18, 2011 3:51 pm

Thanks for the example. I see what you mean now.

On checking here, I can see we have a change-request logged that raises a similar point, so I will add the details of this post to that.

I'm not sure when that's likely to be implemented unfortunately but for now it looks like there isn't a way around the problem.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby tharman » Tue Jan 18, 2011 11:05 pm

Thanks for confirming this is an issue.

I look forward to the next releases which will address this annoyance!

Mostly it annoys me because in that example it looks like I have 2 indexes covering the same columns, one is unique and one non-unique! :shock:
tharman
 
Posts: 3
Joined: Fri Jan 14, 2011 12:47 am

Ditto

Postby AdamY » Thu Jan 20, 2011 12:51 am

Thanks for posting this. We use lots of indexes with included columns so the documentation is very confusing. I also look forward to seeing a fix.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm


Return to SQL Doc 2

Who is online

Users browsing this forum: No registered users and 0 guests