Testing the assumption that a covering index would negate the impact of the sort operator cause by ordering on an expression (rowrank).

The test table... Just under 1/2 M rows similar to the OP's test data...

IF OBJECT_ID('tempdb..#Countries') IS NOT NULL

DROP TABLE #Countries;

;WITH Data (c) AS (

SELECT d.c FROM ( VALUES

('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),('C1'),

('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),('C2'),

('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),('C3'),

('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),('C4'),

('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),('C5'),

('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),('C6'),

('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),('C7'),

('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),('C8'),

('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),('C9'),

('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),('C10'),

('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),('C11'),

('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),('C12'),

('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),('C13'),

('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),('C14'),

('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),('C15'),

('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),('C16'),

('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),('C17'),

('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),('C18'),

('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),('C19'),

('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),('C20'),

('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),('C21'),

('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),('C22'),

('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),('C23'),

('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),('C24'),

('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),('C25'),

('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),('C26'),

('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),('C27'),

('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),('C28'),

('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),('C29'),

('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),('C30'),

('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),('C31'),

('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),('C32'),

('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),('C33'),

('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),('C34'),

('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),('C35'),

('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),('C36'),

('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),('C37'),

('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),('C38'),

('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),('C39'),

('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40'),('C40')

) d (c)

)

SELECT

ISNULL(ROW_NUMBER() OVER (ORDER BY d1.c), 0) AS ID,

d1.c AS Country

INTO #Countries

FROM Data d1, Data d2;

Add the covering index that is the subject of the test. In this case, no reason to not just make it a clustered primary key...

ALTER TABLE #Countries ADD CONSTRAINT pk_tmpCountries PRIMARY KEY CLUSTERED (Country, ID);

The test script (modeled after Luis's solution)

;WITH cte AS (

SELECT

c.ID,

c.Country,

(ROW_NUMBER() OVER (PARTITION BY c.Country ORDER BY c.Country) + 2) / 3 AS RowRank

FROM

#Countries c

)

SELECT

cte.ID,

cte.Country,

DENSE_RANK() OVER (ORDER BY cte.Country, cte.RowRank) AS CountryRank

FROM

cte

All tests were executed with "Discard results after execution" turned on to eliminate display rendering from the execution times...

The 1st 5 executions were exactly as shown above...

The 2nd 5 were were executed with ", cte.RowRank" removed... (all else identical)