SQL Server Nested Loop Join Operator – Revisiting: Part1

Hi Friends,

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).

NSCS_CISeekToolTipNSCS_SeekToolTip

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.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *