Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is this Index Duplicate Expand / Collapse
Author
Message
Posted Saturday, September 12, 2009 5:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 9, 2014 2:13 PM
Points: 138, 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
Post #786844
Posted Saturday, September 12, 2009 7:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #786862
Posted Sunday, September 13, 2009 8:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 9, 2014 2:13 PM
Points: 138, Visits: 198
thanks for the link, will get back to you after reading the same.

Cheers
Siddarth
Post #787018
Posted Monday, September 21, 2009 1:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 9, 2014 2:13 PM
Points: 138, Visits: 198
I went through the link but was not able to get much.

cheers
Siddarth
Post #791397
Posted Monday, September 21, 2009 1:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 20,744, Visits: 32,560
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.




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)
Post #791406
Posted Monday, September 21, 2009 3:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 9, 2014 2:13 PM
Points: 138, 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
Post #791448
Posted Monday, September 21, 2009 7:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 20,744, Visits: 32,560
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.



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)
Post #791481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse