Conditional operator in SQL Server execution plan

The Conditional Operator appears in the execution plan when the query includes logical expressions or conditional evaluations, such as CASE statements or Boolean expressions (AND, OR, IS NULL, etc.). This operator enables SQL Server to make row-by-row decisions based on conditions, allowing it to return results that depend on specific criteria. The Conditional Operator can be identified in the execution plan as it evaluates each row according to the logical condition defined in the query.

See the example

CREATE TABLE Employees (     EmployeeID INT PRIMARY KEY,     Name VARCHAR(50),     Salary DECIMAL(10, 2),     Department VARCHAR(50) );  INSERT INTO Employees (EmployeeID, Name, Salary, Department) VALUES     (1, 'Bagesh', 80000, 'HR'),     (2, 'Rajesh', 90000, 'Finance'),     (3, 'Ganesh', 70000, 'IT'),     (4, 'Mahhesh', 60000, 'Finance'),     (5, 'Ravi', 50000, 'IT');

Table created and records are inserted.

Run the below query.

SELECT employeeid,        NAME,        salary,        department,        CASE          WHEN salary >= 90000 THEN 'High'          WHEN salary >= 70000 THEN 'Medium'          ELSE 'Low'        END AS SalaryCategory FROM   employees;

Run this query and see the execution plan

Here we will not see the conditional operator.

Compute Scalar operator representing the CASE expression. Within the Compute Scalar, SQL Server applies the Conditional Operator to evaluate the conditions row-by-row and assign the appropriate salary category.

The Conditional Operator is used in execution plans to evaluate row-by-row logical conditions, often seen in CASE statements or Boolean expressions. SQL Server’s Compute Scalar operator implements the Conditional Operator by applying conditions to each row and returning the relevant result based on the specified criteria.