mardi 2 décembre 2014

ROW_NUMBER() without PARTITION BY still generates Segment iterator


I'm writing on an upcoming blog post of mine on ranking and aggregate window functions, specifically the Segment and Sequence Project iterators. They way I understand it is that Segment identifies rows in a stream that constitute the end/beginning of a group, so the following query:



SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)


.. uses Segment to tell when a row belongs to a different group than the previous row. The Sequence Project iterator then does the actual row number calculation, based on the output of the Segment iterator's output.


But the following query, using that logic, shouldn't have to include a Segment, because there's no partition expression?



SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)


When I try this hypothesis, however, both these queries use a Segment operator. The only difference is that the second query does not need a GroupBy on the Segment. Doesn't that eliminate the need for a Segment in the first place?


Example



CREATE TABLE dbo.someTable (
someGroup int NOT NULL,
someOrder int NOT NULL,
someValue numeric(8, 2) NOT NULL,
PRIMARY KEY CLUSTERED (someGroup, someOrder)
);

--- Query 1:
SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)
FROM dbo.someTable;

--- Query 2:
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable;




Aucun commentaire:

Enregistrer un commentaire