Segment operator in SQL Server execution plan

When a query is executed in parallel, SQL Server may divided the works into multiple segments, each handled by different worker thread. This segmentation is typically used in conjunction with operations that require partitioned data, such as window functions and aggregate operations. The Segment operator doesn't perform any calculations by itself but prepares the data for downstream operations by marking where each segment (or partition) starts and ends. It allows SQL server to efficiently process large amount of data by leveraging multiple CUP cores.

Icon of Segment Operator

See the example

Creating a table and inserting some data in this table for the demo

CREATE TABLE Sales (     SaleID INT PRIMARY KEY,     ProductID INT,     Quantity INT,     SaleDate DATE ); INSERT INTO Sales (SaleID, ProductID, Quantity, SaleDate) VALUES (1, 101, 5, '2023-01-01'),        (2, 102, 2, '2023-01-02'),        (3, 101, 3, '2023-01-03'),        (4, 103, 7, '2023-01-04'),        (5, 101, 4, '2023-01-05');

Running the below table.

SELECT     SaleID,     ProductID,     Quantity,     SaleDate,     SUM(Quantity) OVER (PARTITION BY ProductID ORDER BY SaleDate) AS CumulativeQuantity FROM Sales;

See the result

See the execution table

 

The Segment operator is crucial for partitioning data in SQL Server, especially with window functions, helping SQL Server handle complex aggregations and maintain efficiency.