ranking function structure

Question

Hi , 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 the operations within this function with T_SQL language. Would you please help me with it.

Answer 1

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.