DB With DB: Book Mark Lookup Key lookup and RID Lookup

In SQL Server execution plans many times we see Key lookup and RID lookup, let us excavate in details what these lookups are and why they appear how we can remove them from execution plans.
1. RID Lookup: when we SELECT any columns (in HEAP Table), which are not part of any indexes but the columns which are in WHERE clause are part of non clustered index then RID (row identifier) lookup happens.
HEAP Table: A Table without clustered index is called HEAP Table

2. Key Lookup: when we SELECT any columns(on Clustered Table), which are not part of any indexes but the columns which are in WHERE clause are part of non clustered index, then Key lookup happens.
When we create a clustered index on a table then it reorder the way records and leaf level of clustered index stores actual data pages with all column values.

When we create non clustered indexes on clustered table (a table with clustered index) then leaf level of each non clustered index store key value of clustered index and key/non key columns values of non clustered index, using this key value of clustered index it perform key lookup and clustered index scan.

When we create non clustered indexes on heap table then, leaf level of each non clustered index store key/non key columns values of non clustered index and 8 bytes row identifier for each row of each non clustered indexes for RID lookup.

Let us consider/check below scenarios.
/*Note: in scenarios i have given perticular blog statement wise like stmt1START,stmt1End and so on*/

/*stmt1START (CREATE Test lookup table)*/

CREATE TABLE TESTLOOKUP
(
EmpId INT,
EmpName VARCHAR(100),
DeptName VARCHAR(100),
Addr VARCHAR(100),
Phone INT
)
/*stmt1END (CREATE Test lookup table)*/

/*stmt2START (INSERT some test rows to verify scenarios)*/
INSERT INTO TESTLOOKUP
SELECT 1 AS ID,'Dinesh' AS NAME, 'IT' AS DEPT, 'DELHI' AS LOC, 1237891 AS PHONE
UNION ALL
SELECT 2, 'Babu', 'COMPUTER', 'MUMBAI', 1236391
UNION ALL
SELECT 3, 'VERMA', 'MGMT', 'PUNE', 1236471
UNION ALL
SELECT 4, 'ALLEN', 'IT', 'NEWYORK', 1647891
UNION ALL
SELECT 5, 'AMIT', 'SALES', 'HYD', 1254891
UNION ALL
SELECT 6, 'ROHAN', 'RETAIL', 'BANGALORE',1234891
UNION ALL
SELECT 7, 'SOHAN', 'IT', 'DELHI', 1236352
UNION ALL
SELECT 8, 'BRYON', 'MGMT', 'GGN', 3456691
GO

INSERT INTO TESTLOOKUP
SELECT TOP 1 Max(EmpId) + 1, 'Dinesh ', 'IT','MUMBAI',1237891 FROM TESTLOOKUP
go 100

INSERT INTO TESTLOOKUP
SELECT TOP 1 Max(EmpId) + 1,'VERMA', 'ITT','DELHI',1237891
FROM TESTLOOKUP
go 3000

/*stmt2END (INSERT some test rows for testing purpose)*/

/*stmt3START (select and check number of records(3108) */
in execution plan, table scan happens below)*/
SELECT *
FROM TESTLOOKUP

/*Execution Plan1*/

/*stmt3END (select and check number of records in execution plan table scan happens)*/

/*stmt4START (select records from table, a table without any indexes,
in execution plan we see table scan for every select stmt) */
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'

/*Execution Plan2*/

SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'

/*Execution Plan3*/

/*stmt4END(select records from table, a table without any indexes,
in execution plan we see table scan for every select stmt) */

/*stmt5START (create non clustered index )*/
CREATE NONCLUSTERED INDEX idxNonClsEmpName
ON TESTLOOKUP(EmpName)

/*stmt5END (create non clustered index )*/

/*stmt6START (select records from the table based on the least value like SOHAN, in execution plan we see RID lookup)*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'

/*Execution Plan4*/

/*stmt6END(select records from the heap table based on the least value like SOHAN, in execution plan we see RID lookup)*/

/*stmt7START (select records from the heap table based on the large value like VERMA, in execution plan we see TABLE SCAN)*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'

/*Execution Plan5*/

/*stmt7END (select records from the table based on the large value like VERMA,
in execution plan we see TABLE SCAN)
*/

/* WHEN LOOKUP & WHEN SCAN?
Now here we have question, like when we search EmpName ='SOHAN' then RID LOOKUP and when we search EMPNAME='VERMA' then table scan, why?
Ans: this is because for SOHAN, selectivity is very high for SOHAN means we have less records like in current data distribution we have only 1 record for SOHAN but for VERMA, selectivity is very less means we have huge records >3000 for VERMA.
when we have very high selectivity optimizer go for LOOKUP but when we have very low selectivity optimizer go for scan and
scan whole table/index rather than searching/lookup a values.
*/

/*stmt8START (create clustered index)*/
CREATE CLUSTERED INDEX IdxClsEmpId
ON TESTLOOKUP(EmpId ASC )

/*stmt8END (create clustered index)*/

/*stmt9START(select records from clustered table based on the least value like SOHAN,
in execution plan we see Key lookup )*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'
/*Execution Plan6*/

/*stmt9END (select records from clustered table based on the least value like SOHAN,
in execution plan we see Key lookup )*/

/*stmt10START (select records from clustered table based on the large value like VERMA,
in execution plan we see clustered index scan)*/
SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'VERMA'

/*Execution Plan7*/

/*stmt10END(select records from clustered table based on the large value like VERMA,
in execution plan we see clustered index scan)*/

/*LET US REMOVE KEY lookup, to remove key lookup add DeptName in
non clustered index or create covering index*/
DROP INDEX idxNonClsEmpName on TESTLOOKUP
CREATE NONCLUSTERED INDEX idxNonClsEmpNameDeptName
ON TESTLOOKUP(EmpName, DeptName)

SELECT EmpId,
EmpName,
DeptName
FROM TESTLOOKUP
WHERE EmpName = 'SOHAN'
/*Execution Plan8*/