Post reply

CI and NC index on same column is it sugestable ?

  • May 5, 2014 at 10:16 am

    #307128

    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 ?

  • May 5, 2014 at 10:55 am

    #1710802

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 5, 2014 at 10:56 am

    #1710803

    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

  • May 5, 2014 at 12:16 pm

    #1710834

    Thanks for your reply

Viewing 4 posts - 1 through 3 (of 3 total)

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