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.

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.

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

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.

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

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)