Composite index

If all of your query patterns include filters on both a and b, and the table gets written to a lot, I would argue for testing a single index that looks like this:

(a,b) include (c,d,e,other include columns)

The reason is it's a single index to maintain rather than three or four, and the selectivity of the third column in the filter probably won't add much additional benefit to individual queries since the first two columns already filter out most of the table (well, really, the first column, since the second column has very few distinct values). You can monitor the queries and if see if any patterns or particular parameter values lead to horribly bad estimates, long runtimes, or missing index warnings. I suspect they won't because, again, based on the density you've quoted, this should lead to very small ranges as long as your queries use filters on a and b or, at the very least, a.

I will stress that where you end up is not going to be a simple "oh, this is clearly what you should do" answer. You will need to test with your hardware, your data, and your query patterns (both read and write) to determine the best index(es) for your workload.


As with all things it depends. Specifically on the cardinality of the columns. If the combination of columns a and b is unique then you really only need the one index (a,b). If it's even close (say < 100 rows each) you'll probably be ok. On the other hand let's say that c has a high cardinality (large number of possible values) and d has a low cardinality (low number of possible values). You might create just create (a,b,c). This will cover the specific query using a,b and c. It will also just as easily cover a query using only a and b. And for a, b and d it will come close. SQL will get close using a and b and then can scan through the remaining rows given that d isn't going to be a whole lot of help anyway.

All things being equal and assuming a reasonable cardinality of each I would create the following.

(a,b,c) (include columns) 

(a,b,d) (include columns) 

(a,b,e) (include columns) 

The query using only a and b will be covered by the others. Assuming that all of the other columns are covered in the include columns of course.