SQL Server: How to best index an N-N Table?

Question

I have a N to N table with columns:

Id (primary key) (I need this for various reasons) ClientId FeatureId

Queries use all combinations of ClientId and FeatureId and need to be quick for all cases. (ClientId+FeatureId, ClientId, FeatureId)

My idea is to create a covered clustured index on ClientId and FeatureId and additional noncovered indexes on ClientId and FeatureId individually? Does this make sense?

Also, I've read somewhere if I create the covered index in the order ClientId, FeatureId I dont have to create and individual index for FeatureId?

Answer 1

I am assuming that the output from the query is clientid and featureid - not the ID field. If you do need this as well, I would suggest investigating 'included columns'.

A covering index on (clientid, featureid)

create index idx_covering on mytable(clientid,featureid)

will result in an INDEX SEEK for both clientid+featureid and clientid queries, with and INDEX SCAN for the featureid query.

Even with another index, just for featureid, the covering index will be used, as the query needs to return both the clientid and featuredid (as per my above assumption).

To get an index seek, create an index with included columns

create index idx_feature_withincluded on mytable(featureid) include (clientid)

Whether or not it is better to have 1 or 2 indexes really depends on your environment - the tradeoff is bewteen maintainability and performance. For every UPDATE and INSERT, the indexes will be updated, but if that is a small overhead in regards to the amount of SELECTs then thats good for you.

It's one of those things that can only really be answered by implementing one way, monitoring and adjusting. Even if you hit an optimum solution, further increase in data volumes or application use can significantly change the way the database performs.