In which scenarios does SQL Server ROW_NUMBER() not start from 1?

Question

We are using ROW_NUMBER() in our query. It is returning correct results in almost all scenarios. But for 1 user, it is behaving very differently.

With #TEST as (
    select top 50 
       ROW_NUMBER() over (order by a.ID) as RN,
       a.ID ID, a.Name Name  
    FROM a 
    where a.name like '%test%')
select * 
from #TEST  
where RN BETWEEN 1 AND 50 
order by RN

This query is working fine for that user when the page size is set as 50. But when the page size is set to 100, we observe that it is not returning all rows. It is just returning only 10 rows. Even though there are more than 100 results satisfying the condition. Please find the below query that is not working correctly.

With #TEST as (
    select top 100 
        ROW_NUMBER() over (order by a.ID) as RN,
        a.ID ID, a.Name Name  
    FROM a 
    where a.name like '%test%')
select * 
from #TEST 
where RN BETWEEN 1 AND 100 
order by RN

When tried to verify for the reason, we observe that the 2nd query returns RN values greater than 100. It does not start from 1.

Can some one explain the probable reason for this behavior. Is there anything to be modified in the syntax or is there any setting to be changed in SQL Server for the row_number() function values to start from 1?

Side comment: I would **NOT** call my CTE some name that starts with a `#` - that character denotes **temporary tables** stored in `TempDB` .... use some other name — marc_s, Nov 16 '11 at 06:04

Mikael Eriksson · Answer 1 · 2011-11-16T06:16:52.967

row_number always starts with one.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

You are doing a select top without an order by clause. That means that you have no control over what rows are returned. You are probably getting a different execution plans that use different indexes to get the rows. The top 100 rows in one plan is not the same top 100 in another plan. Add a relevant order by to the query in the CTE or you can remove the top clause since you are filtering rows in the main query anyway.

In which scenarios does SQL Server ROW_NUMBER() not start from 1?

1 Answers1