Sequence operator in SQL Server execution plan

The Sequence operator in SQL Server is a logical operator in execution plans that ensures multiple operations execute in a specified order. Unlike other operators that perform calculations or modify data, the Sequence operator simply defines the sequence of steps, making sure each operation in the plan completes before moving on to the next one. This operator is often used in MERGE statements or complex INSERT operations where SQL Server needs to ensure that specific actions occur sequentially.

Icon of sequence operator 

Let’s see the example

For this demo we are creating a table and inserting some records.

CREATE TABLE products_demo   (      productid   INT NOT NULL PRIMARY KEY,      productname VARCHAR(50),      price       DECIMAL(10, 2)   );  CREATE TABLE products_demo_stg   (      productid   INT NOT NULL,      productname VARCHAR(50),      price       DECIMAL(10, 2)   ); -- Inserting some records into the both table   INSERT INTO products_demo (ProductID, ProductName, Price)    VALUES     (1, 'Laptop', 1200.00),     (2, 'Smartphone', 800.00),     (3, 'Tablet', 400.00);  INSERT INTO products_demo_stg (ProductID, ProductName, Price)    VALUES     (1, 'Laptop', 52200.00),     (2, 'Keyboard', 300.00),     (5, 'Mouse', 100.00);

 Table created and data inserted successfully.

Now writing marge statement to insert and update the records into the products_demo from products_demo_stg. 

MERGE products_demo AS Target using products_demo_stg AS Source ON Target.productid = source.productid WHEN matched THEN   UPDATE SET Target.price = Source.price,              Target.productname = Source.productname WHEN NOT matched THEN   INSERT (productid,           productname,           price)   VALUES (Source.productid,           source.productname,           Source.price);

Run this script and see the execution plan.

See sequence operator is generated.

To avoid this operator we need to create the primary key on the source table. Let’s create the primary key and run the same query and see the execution plan.

ALTER TABLE products_demo_stg   ADD PRIMARY KEY (productid)

 Let’s run the same and see the execution plan.