About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Saturday, January 24, 2015

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

No comments:

Post a Comment