In which SQL Server scripts does ROW_NUMBER () not start at 1?

Question

We use ROW_NUMBER() in our request. It returns the correct results in almost all scenarios. But for 1 user, he behaves 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 works fine for this user when the page size is set to 50. But when the page size is set to 100, we see that it does not return all the rows. It just returns only 10 rows. Despite the fact that there are more than 100 results that satisfy the condition. Please find the query below that does not work 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 we tried to check the reason, we notice that the second query returns RN values ​​in excess of 100. It does not start with 1.

Can someone explain the probable cause of this behavior. Is there anything that needs to be changed in the syntax, or is there any parameter that needs to be changed in SQL Server for the values ​​of the ROW_NUMBER() function to start with 1?

Answer 1

row_number always starts with one.

Returns the serial number of a line inside the section of the result set, starting with 1 for the first line in each section.

You do select top without an order by clause. This means that you cannot control which rows are returned. You probably get different execution plans that use different indexes to get the rows. The top 100 lines in one plan do not match the top 100 lines in another. Add the corresponding query to the query in the CTE or you can delete the top sentence, since you still filter the rows in the main query.