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.

We often hear indexes explained using the analogy of an index in the back of a book. You want to find the information about “rabbits” for instance – and so you look that up in the back and find the list of pages that talk about rabbits.

The COALESCE and ISNULL T-SQL functions are used to return the first nonnull expression among the input arguments. SQL Server practitioners often wonder what the difference is between the two functions.