SQL Server Segment Operator

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

SegmentGrphPlan

SegmentTextPlan

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.

SegmentToolTip

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *