【Complete Showplan Operators】Chapter 3: Compute Scalar

The previous two chapters covered two of the most important Showplan operators,Concatenation and Assert. It is useful to know about such Showplan operators if you are programming in SQL Server, because they are used by SQL Server's Query Optimizer (QO) to perform a particular operation within a query plan. Each physical operation in the Query Plan is performed by an operator. When you look at a graphical execution plan, you will see each operator represented by an icon. This chapter covers the Compute Scalar Showplan operator. This operator is very common, and we can see it in many execution plans.

As is obvious from its name, Compute Scalar performs a scalar computation and returns a computed value. This calculation can be as simple as a conversion(n. 变换，转变; 改装物; ) of value, or a concatenation(n. 一系列互相关联的事物; 级联) of values.

Most of the time, it is ignored by SQL users because it represents a minimal cost when compared to the cost of the entire(adj. 整个的; 全部的; 全体的; ) execution plan, but, it can become well-worth looking at when we are dealing with cursors(n. 光标;游标;) and some huge loops, and especially if you are having a CPU problem.

To start with, let's take a simple use of Compute Scalar. One simple conversion of data from Int to Char can be done without much problem but, if we execute this conversion one million times, it becomes a different matter. If we change the query so as to not execute this conversion step, we will have an optimization in CPU use, and a consequential(adj. 重要的; 作为结果的，间接的;) improvement in the speed of execution.

Let's take the following query as a sample. The following script will create a table, TabTeste, and populate with some garbage data.

USE tempdb
GO
CREATE TABLE TABTeste(ID Int Identity(1,1) PRIMARY KEY,
Nome VarChar(250) DEFAULT NewID())
GO
SET NOCOUNT ON
GO
INSERT INTO TABTeste DEFAULT VALUES
GO 10000

Now, the code below will pass to the loop one million times.

DECLARE @I INT
SET @I = 0
WHILE @I < 1000000
    BEGIN
        IF EXISTS ( SELECT  ID
                    FROM    TABTeste
                    WHERE   ID = @I )
            BEGIN
                PRINT 'Entrou no IF'
            END
        SET @I = @I + 1;
    END
GO

Graphical execution plan.

As we can see, the operator Compute Scalar is used; let's take a look at the text execution plan to see more details about that operation. 

|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TABTeste].[PK__
TABTeste__3214EC27096F09E1]), SEEK:([tempdb].[dbo].[TABTeste].[ID]=[@I]) ORDERED
FORWARD)

Text execution plan.

This plan is using the Compute Scalar to check if the Nested Loop returns any rows, on the other words; it is doing the IF EXISTS Job.
If we look at the profler(n. 分析器，分析工具；) results we can see the column, CPU, which shows us how much CPU the query above uses.

Now let's change the code to remove the Compute Scalar operator.

DECLARE @I INT ,
    @Var INT
SET @I = 0
WHILE @I < 1000000
    BEGIN
        SELECT  @Var = ID
        FROM    TABTeste
        WHERE   ID = @I
        IF @@ROWCOUNT > 0
            BEGIN
                PRINT 'Entrou no IF'
            END
        SET @I = @I + 1;
    END
GO

Graphical execution plan. 

|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[TABTeste].[PK__TABTeste__3214EC27096F09E1]), SEEK:([tempdb].[dbo].[TABTeste].[ID]=[@I]) ORDERED FORWARD)

Text execution plan.

Now that SQL Server does not use the Compute Scalar, let's take a look at the CPU costs.

As you can see, SQL Server uses less CPU and fnishes the execution of the query faster than it does with the frst query. I'm not trying to show you the better way to check whether a particular value exists, I'm just showing the Compute Scalar behavior.
However, if you have never seen this kind of validation using @@RowCount, it may be that it could help you a little bit in your coding. Some time ago I changed one procedure that uses a lot of IF Exists in much the same way, with very satisfactory result for the performance of the procedure.
Let's take a look at more practical examples of Compute Scalar.

DECLARE @Tab TABLE(ID SmallInt PRIMARY KEY)
SELECT 'Fabiano' + ' - ' + 'Amorim' FROM @Tab

Graphical execution plan. 

|--Compute Scalar(DEFINE:([Expr1003]='Fabiano - Amorim'))
|--Clustered Index Scan(OBJECT:(@Tab))

Text execution plan. 

The plan was generated using the Compute Scalar just to make the concatenation between "Fabiano", "-" and "Amorim". Quite simple.
Now we'll see one very interesting behavior of Compute Scalar that it changes in SQL Server 2005/2008. Consider the following query:

DECLARE @Tab TABLE
    (
      ID SMALLINT PRIMARY KEY
    )
DECLARE @ID_Int INTEGER
SELECT  *
FROM    @Tab
WHERE   ID = @ID_Int

Notice that the Column ID is a SmallInt type, and the variable @ID_Int is a Integer, that means SQL Server as to convert the value of @ID_Int to be able to compare the value with ID Column. At SQL Server 2000 we have the following plans:

SQL 2000 Graphical execution plan.

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003],[Expr1004]))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([@ID_Int])-1,
[Expr1003]=Convert([@ID_Int])+1, [Expr1004]=If (Convert([@ID_Int])-1=NULL) then 0
else 6|If (Convert([@ID_Int])+1=NULL) then 0 else 10))
| |--Constant Scan
|--Clustered Index Seek(OBJECT:(@Tab), SEEK:(@Tab.[ID] > [Expr1002] AND @
Tab.[ID] < [Expr1003]), WHERE:(Convert(@Tab.[ID])=[@ID_Int]) ORDERED FORWARD)

SQL 2000 Text execution plan.

Wow, it's quite hard work, don't you think? Now let's take a look at what happens if we run this code at SQL 2005/2008.

SQL 2005/2008 Graphical execution plan.

|--Clustered Index Seek(OBJECT:(@Tab), SEEK:([ID]=[@ID_Int]) ORDERED FORWARD)

SQL 2005/2008 Text execution plan.

Yep, now we have a much simpler plan (which was nothing about the band). But wait a minute, what is this? Now SQL Server does not convert the value!
Let's look at the execution plan to understand what is going on with the Clustered Index Seek show plan operator.

Graphical execution plan.

As we can see, the SQL Server Dev Team has changed the Engine to use a function called Scalar Operator to convert the value to the appropriate(adj. 适当的；恰当的；合适的) datatype; that's interesting.