SET SHOWPLAN_TEXT ON;
GO
DECLARE @Employees TABLE (emp_nbr INT PRIMARY KEY NONCLUSTERED,
emp_name VARCHAR(35),
ssn CHAR(11) UNIQUE CLUSTERED);
INSERT INTO @Employees VALUES(1, 'Jim Brown', '987-11-0234');
INSERT INTO @Employees VALUES(2, 'Chris Defoe', '987-13-9899');
INSERT INTO @Employees VALUES(3, 'Keith Goldwin', '986-31-1543');
SELECT emp_nbr, emp_name, ssn
FROM @Employees
WHERE emp_nbr = 2;
SELECT emp_nbr, emp_name, ssn
FROM @Employees
WHERE ssn LIKE '998-13%';
--Results
|--Nested Loops(Inner Join, OUTER REFERENCES:([ssn]))
|--Index Seek(OBJECT:(@Employees), SEEK:([emp_nbr]=(2)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:(@Employees), SEEK:([ssn]=[ssn]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:(@Employees), SEEK:([ssn] >= '998-12þ' AND [ssn] < '998-14'), WHERE:([ssn] like '998-13%') ORDERED FORWARD)
Labels: t-sql programming