GilaMonster (6/30/2015)

---

Tac11 (6/30/2015)

---

Now I get on execution plan with 'key lookup', 'nested loop' operator, which I think are bad.

No they're not. There are no bad operators, if there were they wouldn't be in the product. What there are are inappropriate operators for the number of rows the query affects. Since the query in question is going to return a single row, a key lookup is fine.

The index Jason suggests essentially duplicates the table, it has all columns in either key or include. Since for the query in question, the key lookup is fine, there's little sense in duplicating one of the larger tables in the database, just so that a query can execute in maybe 3 less reads. It would be a waste of space and time.

GilaMonster (6/30/2015)

---

Jason A. Long (6/30/2015)

---

(and yes, you are correct... They are bad).

They are not. They are inefficient on large row counts.