Blocking Operator and Non Blocking Operator

Non Blocking Operator: A non blocking operator is one which reads one row from its previous operator and returns the read; Nested loop is a prime example of non blocking operator.

select top 10 pc.ProductCategoryID,pc.Name, psc.ProductSubcategoryID, psc.Name from Production.ProductCategory pc

inner join Production.ProductSubcategory psc

on pc.ProductCategoryID = psc.ProductCategoryID

Blocking Operator: A blocking operator needs to read all the rows from its previous operator to perform  and then return the data. A classic sample of a blocking operator is the SORT operator; it needs to read all rows, sort the data and then return the ordered rows. The execution of the query will wait until all rows to be read and ordered, before continuing with the command

select distinct ProductCategoryID from Production.ProductSubcategory

Here we can see the blocking sort operator reads all rows from clustered index then rearrange the records in ascending fashion and then gives the output to subsequent operator.

Thing starts work, when you are with me… SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share