ROW_NUMBER() without PARTITION BY still generates Segment iterator
I found this 6 year old blog post mentioning the same behavior.
It looks like ROW_NUMBER() always includes a segment operator, whether PARTITION BY is used or not. If I had to guess I would say this is because it makes creating a query plan easier on the engine.
If the segment is needed in most cases, and in the cases where it's not needed it's essentially a zero-cost non-operation, it's a lot simpler to just always include it in the plan when a windowing function is used.
According to the showplan.xsd for the execution plan, GroupBy appears without minOccurs or maxOccurs attributes which therefore default to [1..1] making the element compulsory, not necessarily content. The child element ColumnReference of type (ColumnReferenceType) has minOccurs 0 and maxOccurs unbounded [0..*], making it optional, hence the allowed empty element. If you manually attempt to remove the GroupBy and force the plan you get the expected error:
Msg 6965, Level 16, State 1, Line 29
XML Validation: Invalid content. Expected element(s): '{http://schemas.microsoft.com/sqlserver/2004/07/showplan}GroupBy','{http://schemas.microsoft.com/sqlserver/2004/07/showplan}DefinedValues','{http://schemas.microsoft.com/sqlserver/2004/07/showplan}InternalInfo'. Found: element '{http://schemas.microsoft.com/sqlserver/2004/07/showplan}SegmentColumn' instead. Location: /*:ShowPlanXML[1]/*:BatchSequence[1]/*:Batch[1]/*:Statements[1]/*:StmtSimple[1]/*:QueryPlan[1]/*:RelOp[1]/*:SequenceProject[1]/*:RelOp[1]/*:Segment[1]/*:SegmentColumn[1].
Interestingly I found you can manually remove the Segment operator to get a valid plan for forcing which looks like this:
However when you run with that plan (using OPTION ( USE PLAN ... ) ) the Segment Operator magically reappears. Just goes to show the optimizer only takes the XML plans as a rough guide.
My test rig:
USE tempdb
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.someTable') IS NOT NULL DROP TABLE dbo.someTable
GO
CREATE TABLE dbo.someTable (
someGroup int NOT NULL,
someOrder int NOT NULL,
someValue numeric(8, 2) NOT NULL,
PRIMARY KEY CLUSTERED (someGroup, someOrder)
);
GO
-- Generate some dummy data
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.someTable ( someGroup, someOrder, someValue )
SELECT rn % 333, rn % 444, rn % 55
FROM cte
GO
-- Try and force the plan
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable
OPTION ( USE PLAN N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1000" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.00596348" StatementText="SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable" StatementType="SELECT" QueryHash="0x193176312402B8E7" QueryPlanHash="0x77F1D72C455025A4" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88">
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="131072" EstimatedPagesCached="65536" EstimatedAvailableDegreeOfParallelism="4" />
<RelOp AvgRowSize="15" EstimateCPU="8E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="0.00596348">
<OutputList>
<ColumnReference Column="Expr1002" />
</OutputList>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<!-- Segment operator completely removed from plan -->
<!--<RelOp AvgRowSize="15" EstimateCPU="2E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Segment" NodeId="1" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.00588348">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someGroup" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someOrder" />
<ColumnReference Column="Segment1003" />
</OutputList>
<Segment>
<GroupBy />
<SegmentColumn>
<ColumnReference Column="Segment1003" />
</SegmentColumn>-->
<RelOp AvgRowSize="15" EstimateCPU="0.001257" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00586348" TableCardinality="1000">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someGroup" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someOrder" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someGroup" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someOrder" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Index="[PK__someTabl__7CD03C8950FF62C1]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
<!--</Segment>
</RelOp>-->
</SequenceProject>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>' )
Chop out the XML plan from the test rig and save it as a .sqlplan to view the plan minus the Segment.
PS I wouldn't spend too much time chopping around SQL plans manually as if you know me you would know I regard it as time-eating busy work and something I would never do. Oh hang on!? : )