SQL Server Compute Scalar Operator – Part2

Hi Geeks,

Today we are going to see an interesting behaviour of SQL Server compute scalar operator SQL 2005 onwards. I recommend you to read Part1 before reading this post.

Consider following SQL statement where column @ID is of Smallint data type and @IDInt is of Integer. If we compare these two in WHERE clause, SQL Server has to convert @IDInt to be able to perform comparison. We will run following statement(s) first in SQL 2000 and then SQL 2012 then observe their representation in execution plan.

DECLARE @Table TABLE(ID SmallInt PRIMARY KEY)
DECLARE @IDInt Integer

SELECT *
	FROM @Table
WHERE ID = @IDInt

SQL 2000 Graphical Plan.

CS_SQL2000_GraphiicalPlan

CS_SQL2000_ToolTip

If we decode query plan in text it looks like following image i.e. lot of conversions for comparison.

CS_SQL2000_TextPlan

Let us now find out SQL 2012 Graphical execution plan.

CS_SQL2012_GraphicalPlan

CS_SQL2012_ToolTip

As we can see, SQL Server engine changed to use a function called Scalar Operator to do the conversion instead of multiple use of covert and checks that was seen in SQL 2000.

That’s all for compute scalar operator, I’ll be back tomorrow with a new operator.

Happy learning!

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook

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 *