ranking function structure

I know the DENSE_RANK() function of the ranking function and its job well. But always I only write this function’s name and it do its job implicitly can cannot see how it work explicitly and I like to see its structure which do开发者_如何学Python the operations within this function with T_SQL language. Would you please help me with it.

You can see this from the execution plans.

Example Code

CREATE TABLE #T
(ID INT IDENTITY(1,1) PRIMARY KEY,
G INT NOT NULL,
S INT NOT NULL)

INSERT INTO #T (G, S) VALUES (1,1),(1,1),(1,3),(1,4),(2,1)

SELECT G,S, DENSE_RANK() OVER (PARTITION BY G ORDER BY S) AS D
FROM #T

Execution Plan

Description

It first needs to sort the data in order G,S as there is no suitable index to use which is presorted.

Then there are 2 segment iterators. Segment iterators have a "GROUP BY" property. They output a segment column indicating whether the current row is in the same group as the previous one or not.

The first segment iterator Groups by the partitioning column G and outputs segment column Segment1004.

The second segment iterator Groups by G,S and outputs Segment1005 as the segment column.

The Sequence Project iterator outputs the dense_rank value based on these inputs. It will output 1 if Segment1004 indicates that this is a new partitioning group otherwise it will check the value of Segment1005 to know whether to output the same value as last time or to increment it first.

See this article by Paul White for more