SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is this Index Duplicate


Is this Index Duplicate

Author
Message
Rookee DBA
Rookee DBA
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 198
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
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15504 Visits: 11354
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Rookee DBA
Rookee DBA
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 198
thanks for the link, will get back to you after reading the same.

Cheers
Siddarth
Rookee DBA
Rookee DBA
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 198
I went through the link but was not able to get much.

cheers
Siddarth
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38891 Visits: 38508
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Rookee DBA
Rookee DBA
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 198
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38891 Visits: 38508
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search