Is this Index Duplicate

Question

Post reply

Is this Index Duplicate

Rookee DBA

SSCrazy

Points: 2188
More actions
September 12, 2009 at 5:05 am

#95775

Hi
I have a table RQST with the primary key on Column ID. The Index automatiicaly created is
CREATE UNIQUE CLUSTERED INDEX [IX_RQST] ON [dbo].[RQST]
(
[ID] ASC
)
And I have another index with as follows:-
CREATE NONCLUSTERED INDEX [IX_RQST_SID_RTypeID_RCrBy] ON [dbo].[RQST]
(
[ID] ASC
)
INCLUDE (
[RQSTTypeID],
[SystemID],
[RequestCreatedBy]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Is that a duplication of Index? if yes then how.
I need to have a Covering index so that data can be fetched from index directly instead of table.
cheers
Siddarth

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

I'm assuming that the question you are asking is:

Is the ID column duplicated because it is in the clustered and non-clustered indexes?

The answer is interesting, and the full detail depends on whether the index is declared as UNIQUE or not.

Rather than give the answer directly, I'm going to ask you to read this blog entry by Kalen Delaney.

Try the code out, and see if you can answer your own question - it's the best example I have seen to illustrate the finer points of index structure - particularly as it applies to your question.

Post back if you have further questions after reading the blog.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Rookee DBA SSCrazy Points: 2188 More actions · Answer 2

thanks for the link, will get back to you after reading the same.

Cheers

Siddarth

Rookee DBA SSCrazy Points: 2188 More actions · Answer 3

I went through the link but was not able to get much.

cheers

Siddarth

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

siddartha pal (9/12/2009)
Hi
I have a table RQST with the primary key on Column ID. The Index automatiicaly created is
CREATE UNIQUE CLUSTERED INDEX [IX_RQST] ON [dbo].[RQST]
(
[ID] ASC
)
And I have another index with as follows:-
CREATE NONCLUSTERED INDEX [IX_RQST_SID_RTypeID_RCrBy] ON [dbo].[RQST]
(
[ID] ASC
)
INCLUDE (
[RQSTTypeID],
[SystemID],
[RequestCreatedBy]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Is that a duplication of Index? if yes then how.
I need to have a Covering index so that data can be fetched from index directly instead of table.
cheers
Siddarth

Actually, they are identical as both indexes are on the same column, [ID]. Also, a clustered index is a covering index by definition as the leaf nodes of a clustered index is the data.

Rookee DBA SSCrazy Points: 2188 More actions · Answer 5

The first index was created when primary key was created on ID column. So it means, instead of creating a new index, I just need to modify the existing index?

Also the first index is unique clustered index and 2nd one is non-unique non-clustered index.

cheers

Siddarth

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

siddartha pal (9/21/2009)
The first index was created when primary key was created on ID column. So it means, instead of creating a new index, I just need to modify the existing index?
Also the first index is unique clustered index and 2nd one is non-unique non-clustered index.
cheers
Siddarth

First, no, you don't need to modify the first index. Second, yes, the second index is declared as a non-unique index but it is by nature a unique index because it is define on the same column as the primary key which is also the first clustered index. As such, the second index will never have a duplicate value for ID.