So from what I understand, if I want a covering index for this query it either has to include all of the columns in SomeTable to avoid a lookup, or I need to keep the # of included columns minimal so that the cost of the lookup isn't considered too expensive and the index isn't used at all?
Including some columns is as good as including none. The number of columns is irrelevant. Fro SQL, looking up 1 column is like looking up 20. It's the number of rows that it must look up that's important
You're said nothing about the selectivity of those queries. Quoting the portion of the article that you quoted (emphasis mine)
...the index must be either covering or return sufficiently small number of rows that the required lookups to the clustered index/heap (to retrieve the remainder of the columns) is not considered too expensive by the optimiser.
So... How many rows are those returning? 1? 0.001% of the table? 50% of the table?
And on a query like the "find" one above, the lookups are done per row found?
Lookups are always done per row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability