KEY Lookup & RID Lookup

In sql server there are lot of terms which confuse the developer many times about the functionality but while understanding indexing it is very important to understand these as by properly using it the query performance could be increased.

Here i will quickly discuss about two terms i.e. KEY LOOKUP(Previously known as bookmark lookup) and RID Lookup.

KEY LOOKUP:

If a table has a clustered index on it is then it is called as KEY LOOKUP. In sql server data is stored is stored in Binary Tree format & by following this way data is stored. So here the table having a clustered index have a key point to data information.

RID LOOKUP (ROW IDENTIFIER LOOKUP):

In a case where a table does not have clustered index, but the non-clustered index is created on it then it is called RID lookup.

This operation is very expensive as syetem needs to scan through the B-Tree heap with the row Identifier.

 

The best suggested for overcoming these scenarios is to have a clustered index on a table.