SQL SERVER – Computed Column and Compute Scalar Operators

What I enjoy the most in my daily life is to help different organizations with performance tuning via the Comprehensive Database Performance Health Check. One of my clients recently asked me a very interesting question about the computed column and Compute Scalar Operators. Let us learn about it today.

SQL SERVER - Computed Column and Compute Scalar Operators ComputeScalarOperators-800x251

My client asked me a very interesting question about the computed column. The question is as follows:

When any computed column is retrieved from a table, why are there two scalar operations in the execution plan?

Well before we understand this, let us set up a table so we can understand the question better.

First, let us create a table and populate it with some sample data in it.

USE tempdb
GO
CREATE TABLE Test (ID INT, Col1 VARCHAR(100))
GO
INSERT INTO Test (ID, Col1)
VALUES (1, 'One')
GO

Next let us run the following select statement and also when we run it, keep the execution plan enable.

SQL SERVER - Computed Column and Compute Scalar Operators computedscalar1

From the execution plan it is very clear that there are only two operators one is select and another one is table scan. Now let us go ahead and add a computed column to the same table.

ALTER TABLE TEST 
ADD Col2 AS ID+ID
GO

Now let us select the column which has been just added to the table, keeping the execution plan enabled.

SELECT ID, Col1, Col2
FROM Test
GO

SQL SERVER - Computed Column and Compute Scalar Operators computedscalar2

Let us now check the execution plan. In this execution plan, you will see two Compute Scalar Operators.

The question is as there is only one computed column in the table, why are there two compute scalar operations?

The answer is very simple and we can easily figure them out by right-clicking on the operator itself and expanding the properties. Here is the property of each of the scalar operations.

The first from the RIGHT Compute Scalar operation actually does the arithmetic operation of the ADD of the column from the table.

SQL SERVER - Computed Column and Compute Scalar Operators computedscalar3

The first from the LEFT Compute Scalar operation just displays the value which has been passed from the previous function as a scalar string.

SQL SERVER - Computed Column and Compute Scalar Operators computedscalar4

Well, that’s it. I hope this explains why you see two different compute scalar when you retrieve the computed column. If you have any further information about it, please leave a comment and I will publish it with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Disable Statistics Update on a Specific Table
Next Post
SQL SERVER – View Percentage Completed for A Long Executing Query

Related Posts

Leave a Reply

Menu