Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CI and NC index on same column

Author  Topic 

bala012
Starting Member

4 Posts

Posted - 2014-05-05 : 12:14:14
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 ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-05 : 12:38:33
Indexes have overhead. So, yes there is some performance hit for everything that changes the data. If the indexes are exactly the same, then remove the non-clustered one. If it is different somehow, then it might make sense to keep it. But, in general it is not a good idea to have a clustered and an non clustered index on the same column.
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-07 : 05:38:30
Be aware that removing the NC index can cause blocking if foreign keys are used.

Placing a clustered and non-clustered index on a column is the only way to stop foreign key lookups and insert/update/delete blocking each other in a high transaction environment.
Go to Top of Page
   

- Advertisement -