Two nonclusterd index creates any performance issue or not

Ravi Shekhar

13y1.5k

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 c1,c2,c3 present in 3 nonclustered index. due to this is there any performance issue or Not.

Thanks in advance

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted 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
0
Jignesh Trivedi13y ago
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.
0