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.
If we decode query plan in text it looks like following image i.e. lot of conversions for comparison.
Let us now find out SQL 2012 Graphical execution plan.
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 FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
