The Compute Scalar Operator in SQL Server execution plans is used to compute new values for each row based on existing data or expressions in the query. These computations can include things like evaluate expression, arithmetic operations, type conversion, string manipulations, function calls, or column value transformations.
Icon of Compute scalar
See the example
Arithmetic Expression: See below query
|
select ProductID,Name,StandardCost,ListPrice from Production.Product |
See the execution plan
This execution plan is simple.
Let’s add Arithmetic Expression in the query. Here we are calculating the total
price as standardcost*ListPrice. See below
Compute scalar is generated. See
the property.
See the example for
type conversion
Here we are converting the ListPrice column value to varchar
from decimal.
|
select
ProductID,Name,StandardCost,cast(ListPrice as varchar(100)) as ListPrice from Production.Product |
Se the property of compute scalar.
Build in function or User Defined function (UDF):
In this example we are using the Build in function LEN to
find the length of the name.
|
select ProductID,Name,LEN(Name) as Size_of_Name from Production.Product |
The Compute Scalar Operator is usually lightweight and does not significantly impact query performance in most cases. However, there are some situations where the Compute Scalar operator can become a bottleneck, especially if we are using build in function or UDF or Type conversion. See In the above example we cost of completed scalar is 0 but here cost 1.
For the performance point of view
avoid Expensive Scalar UDFs and If possible, replace scalar user-defined
functions with inline table-valued functions (TVFs), which are generally more
efficient because they avoid row-by-row processing. Also we can Minimize Type
Conversions and avoid implicit conversions whenever possible by explicitly
specifying the correct data types in our schema and queries. Pre compute Values
in Views or Computed Columns if possible.
