Index with Included Column

• 
  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 B January 17, 2011 08:48

  - 0 +
• 
  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

  tharman January 17, 2011 17:49

  - 0 +
• 
  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 B January 18, 2011 09:51

  - 0 +
• 
  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 January 18, 2011 17:05

  - 0 +
• 
  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 January 19, 2011 18:51

  - 0 +

Add comment

Please sign in to leave a comment.