Operators - Revisited

I shifted my focus to Linux-java. I always love SQL Server :). Thanks to Roji & Balmukund my SQL mentors. This blog post is on operators. This is based on my earlier presentation on SQLCommunity site link

Operators

• Fundamental Unit of Execution
• Blocking (Hash, Sort – In memory Operators) & Non-Blocking (Seeks, Scans etc..)
• Common Operators – Seek, SCAN, Bookmark Lookup, JOINs (Nested, Merge, Hash)
• Execution Plan need to be interpreted from right to left
• An operator can have More than One Input and One Output

Following different kinds of operators we will check

•  Join Operators
•  Bookmark lookup operator
•  Seek & Scan Operator
•  Spool Operator
•  Option for (Hint)
•  Fast N (Hint)

Example Walkthru
--STEP 1

CREATE TABLE TSCAN
(
    Col1 INT IDENTITY(1,1),
    Col2 VARCHAR(40)
)

--STEP 2
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    INSERT INTO TSCAN(Col2)
    VALUES(CONVERT(VARCHAR(20),@I)+'VALUE')
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 3 (Table SCAN)

SELECT Col1 FROM TSCAN WHERE Col1 = 100

Clustered Index Seek
--STEP 4
--Now Lets Create an Index on Col1
CREATE CLUSTERED INDEX CIX_TSCAN on TSCAN(Col1)

--STEP 5 (Clustered Index Seek)
SELECT Col1 FROM TSCAN WHERE Col1 = 100

--STEP 6 (Clustered Index SCAN)
SELECT Col1, Col2 FROM TSCAN WHERE Col2 = '100Value'

--STEP 7 (Demo Bookmark Lookup)

ALTER TABLE TSCAN
ADD COL3 CHAR(100)
--Populate Data
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    UPDATE TSCAN
    SET COL3 = (CONVERT(VARCHAR(20),@I)+'COL3')
    WHERE Col1 = @I
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 8 (Bookmark Lookup)
CREATE INDEX IX_COL3 ON TSCAN (COL3)
SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'

--STEP 9 (Resolving Key Lookup)

--Create Index on Col3, Col2 for above query to result in Index Seek
DROP INDEX TSCAN.IX_COL2_COL3
CREATE INDEX IX_COL2_COL3
ON TSCAN(COL3)
INCLUDE (COl2)

SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'

(Working with JOINS)

--NESTED LOOPS (Smaller Inner Sets, Join Columns Indexed)
CREATE TABLE NLOOPTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE NLOOPTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @I INT
DECLARE @J INT
SET @I = 1
SET @J = 1
WHILE 1 = 1
BEGIN
    IF @I < 1000
    INSERT INTO NLOOPTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    IF @J < 100000
    INSERT INTO NLOOPTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @I = @I + 1
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--NESTED LOOP
SELECT
N1.Col1, N2.Col2
FROM NLOOPTable1 N1
JOIN NLOOPTable2 N2
ON N1.Col1 = N2.Col1

--Merge JOIN

--TWO Sorted (Indexed) Tables, Large Tables SQL Server Chooses Merge Join when the Join columns are indexed
CREATE TABLE MergeJOINTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE MergeJOINTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO MergeJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Merge JOIN
SELECT
N1.Col1, N2.Col2
FROM MergeJOINTable1 N1
JOIN MergeJOINTable2 N2
ON N1.Col1 = N2.Col1

 Hash Join
--Two Large Unindexed Tables, No Indexes Defined on Join Columns

CREATE TABLE HashJOINTable1(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE HashJOINTable2(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO HashJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Hash JOIN
SELECT
N1.Col1, N2.Col2
FROM HashJOINTable1 N1
JOIN HashJOINTable2 N2
ON N1.Col1 = N2.Col1

Index Recommendations in Green you can find. Happy Reading!!!!