The Non-Blocking Segment Operator

Hugo Kornelius notes a documentation bug with the Segment operator:

The Segment operator, like all operators, is described at the Books Online page mentioned above. Here is the description, quoted verbatim:

Segment is a physical and a logical operator. It divides the input set into segments based on the value of one or more columns. These columns are shown as arguments in the Segment operator. The operator then outputs one segment at a time.

Looking at the properties of the Segment operator, we do indeed see the argument mentioned in this description, in the Group By property (highlighted in the screenshot). So this operator reads the data returned by the Index Scan (sorted by TerritoryID, which is required for it to work; this is why the Index Scan operator is ordered to perform an ordered scan), and divides it into segments based on this column. In other words, this operator is a direct implementation of the PARTITION BY spefication. Every segment returned by the operator is what we would call a partition for the ROW_NUMBER() function in the T-SQL query. And this enables the Sequence Project operator to reset its counters and start at 1 for every new segment / partition.

Read on to understand the issue and see Hugo’s proof.

Related Posts

Permission Set In A Post-SQL 2017 CLR World

Solomon Rutzky investigates what the PERMISSION_SET property does as of SQL Server 2017: And farther down on that same page, there is a note stating:  Important The PERMISSION_SET option is affected by the clr strict security option, described in the opening warning. When clr strict security is enabled, all assemblies are treated as UNSAFE. The last sentence in each of those quoted statements […]

Read More

Thoughts On UTF-8 Encoding In SQL Server 2019

Solomon Rutzky digs into UTF-8 support in SQL Server 2019 and has found a few bugs: Let’s start with what we are told about this new feature. According to the documentation, the new UTF-8 Collations: can be used … as a database-level default Collation as a column-level Collation by appending “_UTF8” to the end of […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30