Index with Included Column

Comments

• James B Redgate › Posts: 1,124 Silver 4

  January 17, 2011 8:48AM

  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?

  Systems Software Engineer

  Redgate Software

  0 · Share on Twitter
• tharman Posts: 4

  January 17, 2011 5:49PM

  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

  0 · Share on Twitter
• James B Redgate › Posts: 1,124 Silver 4

  January 18, 2011 9:51AM

  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.

  Systems Software Engineer

  Redgate Software

  0 · Share on Twitter
• tharman Posts: 4

  January 18, 2011 5:05PM

  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:

  0 · Share on Twitter
• AdamY Posts: 55 Bronze 3

  January 19, 2011 6:51PM

  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.

  0 · Share on Twitter