Constant Scan operator in SQL Server execution plan

When our query evaluate a constant expression then execution plan generate constant scan. I means it occur when query involves a constant expression that does not require scanning any actual table.

It generate on below scenario’s 

Ø  Expression with static values

Ø  Generating Rows without a table

Ø  Insert with constant value

Icon of Constant Scan Operator

Let’s see the example

Generating Rows without a table

Run the below query

SELECT 'Bagesh' as name UNION All SELECT 'Kumar' as name

If we use Union then our execution plan will be different see below.

SELECT 'Bagesh' as name UNION  SELECT 'Kumar' as name

  

Because union combine the two result set and remove the duplicate record. In this example we don’t have duplicate record. Let’s add a duplicate records and see the execution plan.

SELECT 'Bagesh' as name UNION  SELECT 'Kumar' as name UNION SELECT 'Kumar' as name

  

 Insert with constant value

See the below example

Creating a table

CREATE TABLE tbl_constant_scan_demo   (      id            INT IDENTITY(1, 1),      default_value INT DEFAULT 100   );

Now inserting a record into this table.

INSERT INTO tbl_constant_scan_demo DEFAULT VALUES;

See the execution plan