Red Gate forums :: View topic - Index with Included Column
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Doc 2
SQL Doc 2 forum

Index with Included Column

Search in SQL Doc 2 forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
tharman



Joined: 14 Jan 2011
Posts: 3

PostPosted: Fri Jan 14, 2011 12:55 am    Post subject: Index with Included Column Reply with quote

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?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Mon Jan 17, 2011 2:48 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
tharman



Joined: 14 Jan 2011
Posts: 3

PostPosted: Mon Jan 17, 2011 11:49 pm    Post subject: Reply with quote

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:

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Tue Jan 18, 2011 3:51 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
tharman



Joined: 14 Jan 2011
Posts: 3

PostPosted: Tue Jan 18, 2011 11:05 pm    Post subject: Reply with quote

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! Shocked
Back to top
View user's profile Send private message
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Thu Jan 20, 2011 12:51 am    Post subject: Ditto Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group