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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:49 PM
Points: 13, Visits: 460
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
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 Query Performance Tuning
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 4,392, Visits: 10,677
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1567627
Posted Monday, May 5, 2014 12:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:49 PM
Points: 13, Visits: 460
Thanks for your reply
Post #1567665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse