November 17, 2009 at 5:07 am
Hi
this is the table script
CREATE TABLE [dbo].[RequestSubTypes](
[RequestSubTypeID] [int] IDENTITY(1,1) NOT NULL,
[RequestTypeID] [int] NOT NULL,
[RequestSubTypeName] [varchar](50) NOT NULL,
CONSTRAINT [PK_RequestSubTypes] PRIMARY KEY CLUSTERED
(
[RequestSubTypeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
And the indexes are as follows:-
ALTER TABLE [dbo].[RequestSubTypes] ADD CONSTRAINT [PK_RequestSubTypes] PRIMARY KEY CLUSTERED
(
[RequestSubTypeID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [IX_RequestSubTypes] ON [dbo].[RequestSubTypes]
(
[RequestSubTypeID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Column RequestSubTypeID is a identity column, then why the index IX_RequestSubTypes( non clustered ) was created, I am not able to understand the same. It appears that it's been created by mistake.
Could some one explain the same?
thanks in advance.
November 17, 2009 at 6:25 am
It's not the fact that the column is an identity that makes the second index a 'mistake', it's the fact that there's both a clustered index and a nonclustered index on the same column. While there are some circumstances where this may be desirable, in the majority of cases, it's a mistake and the nonclustered index is unnecessary
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 6:50 am
I agree with Gail. You don't usually need duplicate indexes.
November 17, 2009 at 8:08 am
Gentlemen, thanks alot for your comments.
Do you mind sharing the circumstances in which both the index will be required? I am not able to anticipate the same.
November 17, 2009 at 8:24 am
Rookee DBA (11/17/2009)
Gentlemen, thanks alot for your comments.
Only one gentleman here, and that's Steve.
Do you mind sharing the circumstances in which both the index will be required?
Don't worry about it. Consideration is the size and depth of the index. Cluster will be the biggest most of the time. Only time to 'double' the index is if you absolutely have to have a small index on the clustering key. As I said, don't worry about it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 10:42 am
Hi Gail,
thanks for your valuable comments.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply