Split operator in SQL Server execution plan

Split Operator is used in parallel execution plan. It split the data into to the multiple threads. The Split operator in SQL Server execution plans is used in scenarios where SQL Server needs to split a single row into multiple rows. This operator is most commonly associated with merge operations like MERGE or INSERT.

The Split operator's purpose is to break down a complex data structure or set of data into multiple smaller rows that can be processed independently. For example, when performing operations like MERGE, it splits rows based on how SQL Server needs to handle updates, inserts, and deletes for individual columns or indexes.

Read: MERGE Statement in SQLServer to insert, update and delete at the same time

https://bageshkumarbagi-msbi.blogspot.com/2018/11/merge-statement-in-sql-server-to-insert.html

Icon of Split Operator

See the example

For this demo creating destination  table as below

CREATE TABLE salesorderdetail   (   salesorderdetailid    INT IDENTITY(1, 1) NOT NULL,   salesorderid          INT NOT NULL,   carriertrackingnumber NVARCHAR(25) NULL,   orderqty              SMALLINT NOT NULL,   productid             INT NOT NULL,   specialofferid        INT NOT NULL,   unitprice             MONEY NOT NULL,   unitpricediscount     MONEY NOT NULL,   linetotal             NUMERIC(38, 6) NOT NULL   )

Table created successfully.

Here we are using the Source as adventureworks2019.sales.salesorderdetail 

Now we are inserting records into salesorderdetail table using merge statement and see the execution plan.

MERGE INTO salesorderdetail AS Target using adventureworks2019.sales.salesorderdetail AS Source ON Target.salesorderdetailid = source.salesorderdetailid WHEN matched THEN   UPDATE SET Target.unitpricediscount = Source.unitpricediscount WHEN NOT matched THEN   INSERT (salesorderid,           carriertrackingnumber,           orderqty,           productid,           specialofferid,           unitprice,           unitpricediscount,           linetotal )   VALUES (Source.salesorderid,           Source.carriertrackingnumber,           Source.orderqty,           Source.productid,           Source.specialofferid,           Source.unitprice,           Source.unitpricediscount,           Source.linetotal );

 Let’s run this script and see the execution plan.