I have a table with a lot of columns. In a particular query being generated by a web API it selects 23 columns of data. With 4 of those columns in the where clause. I am assuming that in order to avoid any bookmark lookups that I would need to create an index on the most selective column and then include the remaining 22 columns as included columns. Is this the correct approach? I ask because in my testing the only way I have avoided RID lookups is by including all the columns as included columns and the primary index being on a very selective column. Any pointers, gotcha's and assistance is very much appreciated.
23 columns is a lot to store on the page of an index, depending on the data we're talking about. However, if you are doing point lookups on this, then it might be worth it. You'll need to test to be sure. Instead of a single column as the key, I'd probably plan on using all four columns from the WHERE clause in the key. As a starting point for testing, yes, I'd pick the most selective column, but I would test with other columns too.