This is a follow up post of my previous blog posts on SQL Server Nested Loop Join operator Part1 and Part2. I got a question in one of the social networking website asking how Nested Loop operators behaves when outer inputs are small i.e. uses a Seek operation opposed to a Scan and inner input also indexed?
To answer this question let us consider following SQL query as an example.
USE [AdventureWorks2012] SELECT SD.SalesOrderID, PR.Name, PR.Class FROM Production.Product PR JOIN Sales.SalesOrderDetail SD ON PR.ProductID = SD.ProductID WHERE PR.ProductID = 776
Above figure is different from previous scenario (Please refer Part1 and Part2) as it includes seek operations both in outer and inner inputs. From the query plan, SQL Server query optimizer retrieved a single row for ProductID 776 using a Clustered Index Seek on Product Table and then all matching rows are being retrieved using a Non-clustered Index from SalesOrderDetail table (see ToolTip(s) below).
Note, SQL Server query optimizer is smart to duplicate filter for ProductID on both Product as well as SalesOrderDetail tables and could sense join condition is based on the same column.
See you tomorrow, till then.