Index with Included Column

tharman

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?

James B

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?

tharman

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

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

James B

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.

tharman

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:

AdamY

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.