SQL Server segment operator is a physical as well as logical operator. It divides its inputs into different segments based on their values and outputs one segment at a time. Segment Operator, divides the data into different groups and just needs to read the rows until all groups are formed properly and continues until all data is read.
Let us understand this operator with the help of following query;
WITH MainQuery AS
Max_qty = MAX(INV.Quantity)
Production.ProductInventory AS INV
FROM MainQuery AS MQ
INNER JOIN Production.ProductInventory AS INV ON
INV.Shelf = MQ.Shelf
AND INV.Quantity = MQ.Max_qty
Clustered Index Scan operator produces desired rows as an input to Sort operator which is then sorted in the order of Shelf and Quantity columns. Segment operator then detects when new groups arrive from clustered index scan operator and partitions rows into groups checking if the current row belongs to the same group as the previous row. This operator expects incoming rows in order. This operator has a group by argument to specify to partition its input. This adds an additional column and in our example it is Segment 1006 and can be visible in ToolTip.
Output from this Segment is then passed to Top operator. Finally, query result is sorted by Sort operator in the order of Shelf column.
See you tomorrow here on One operator a day.