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

CI and NC index on same column is it sugestable ? Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:53 PM
Points: 9, Visits: 449
Hi,

I have a question regarding indexes.

If i have cluster and NC index on same column,does it degrade performance on DML statements ? any advantage on select statements.

Is it good to have both indexes on same column ?
Post #1567598
Posted Monday, May 5, 2014 10:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 15,664, Visits: 28,062
In the overriding majority of cases, no, this is a serious waste of resources. Most queries will only ever reference the clustered index, especially if they need to get any columns from the data that is stored there. You might find some odd cases such as a COUNT(*) or something like that where no other columns are ever referenced that the non-clustered index, because it will be smaller, with fewer pages, than the clustered index (assuming a table with multiple columns of data). But those are going to be very tiny and obscure exceptions. In the mean time, you're maintaining a second set of information on your storage system for every INSERT/UPDATE/DELETE that will never be used.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1567626
Posted Monday, May 5, 2014 10:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 5,018, Visits: 10,538
does it degrade performance on DML statements ?
Is it good to have both indexes on same column ?


In general, it's not a good thing. In case of a clustered index, the index is the data in the table. In case of a nonclustered index, the index contains a copy of the data. Each time you write to the table, you write multiple copies of the data and that can hurt performance.

any advantage on select statements?


Generally speaking very little advantage.
The only advantage could come from the nonclustered index being smaller than the clustered index when a scan is performed. Often, this advantage is eliminated by the subsequent lookups needed to fetch the columns not included in the nonclustered index.

Bottom line: there are some cases where a nonclustered index and a clustered index on the same key are beneficial, but it's very unlikely.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1567627
Posted Monday, May 5, 2014 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:53 PM
Points: 9, Visits: 449
Thanks for your reply
Post #1567665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse