Two nonclusterd index creates any performance issue or not

Question

hi seniors I have a question. I am bit confused. Suppose I have created 5 nonclusterd index in multiple columns. if they contain repeated columns i.e. column c1c2c3 present in 3 nonclustered index. due to this is there any performance issue or Not. Thanks in advance

Iftikar Hussain · Answer

Hi If you are created nonclusterd indexes on your data and they are constructed cleverly they will be reused. e.g. imagine you search a table on three columns state county zip. you sometimes search by state only. you sometimes search by state and county. you frequently search by state county zip. Then an index with state county zip. will be used in all three of these searches. If you search by zip alone quite a lot then the above index will not be used(by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful. You could then create an index on Zip alone that would be used in this instance. Regards Iftikar

Jignesh Trivedi · Answer

hi Generally nonclustered indexes shell be designed to improve the performance of frequently used queries those are not covered by the clustered index. It improve performace of your select Query but if you create more noncluster index on table it might degrade performace of Insert and Update query. Other point to remember Create non cluster index on table column which is match with following considerations * use Join or group by or where clauses. * create on columns which is frequently involved in search conditions of a query hope this will help you.