Therefore I thought I would give a quick explanation for anyone still unsure what a Key Lookup is actually doing and why it’s considered bad.

We all know that Non-Clustered indexes all include the “Clustered Key” of the table. If not, then you do now. And if you have no Clustered Key (ie. Your table is a Heap) then the non-clustered will contain a Row Identifier which is unique and refers back to a specific record within your Heap. This is how a non-clustered index is linked to your table… whether it be via the Clustered Key or the RID.