ALZDBA (12/24/2013)
all NCI access that needs extra data will use the clustered index to fetch that data.what columns do you expect to get used ?
Provide indexes for those columns and try to figure out if you can use included columns for index only access.
As a general guideline I suggest to provide FK indexes, unless proven they hurt your data systems performance.
As a status column datatyped bit, maybe a filtered index can reduce IO quite a bit depending on the distribution ratio .
Should I drop unique constraint and make non clustered index only or add more column in unique constraint instead of creating another nonclustered index?
Also columns included in unique constraint if appear in non clustered index then will it make effect?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/