Hi Geeks,
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;
USE [AdventureWorks2012]
WITH MainQuery AS
(
SELECT
INV.Shelf,
Max_qty = MAX(INV.Quantity)
FROM
Production.ProductInventory AS INV
GROUP BY
INV.Shelf
)
SELECT
INV.ProductID,
INV.LocationID,
INV.Shelf,
INV.Quantity,
INV.ModifiedDate
FROM MainQuery AS MQ
INNER JOIN Production.ProductInventory AS INV ON
INV.Shelf = MQ.Shelf
AND INV.Quantity = MQ.Max_qty
ORDER BY
INV.Shelf
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.
Happy Learning.
Regards,
Kanchan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
