ROW_NUMBER Without ORDER BY

user_7337831 0
Viewed 105325

I've to add row number in my existing query so that I can track how much data has been added into Redis. If my query failed so I can start from that row no which is updated in other table.

Query to get data start after 1000 row from table 

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (Order by (select 1)) as rn ) as X where rn > 1000

Query is working fine. If any way that I can get the row no without using order by.

What is select 1 here?

Is the query optimized or I can do it by other ways. Please provide the better solution.

window-functions tsql sql-server sql
4 Answers

What is select 1 here?

In this scenario, the author of query does not really have any particular sorting in mind. ROW_NUMBER requires ORDER BY clause so providing it is a way to satisfy the parser.

Sorting by "constant" will create "undeterministic" order(query optimizer is able to choose whatever order it found suitable).

Easiest way to think about it is as:

ROW_NUMBER() OVER(ORDER BY 1)    -- error
ROW_NUMBER() OVER(ORDER BY NULL) -- error

There are few possible scenarios to provide constant expression to "trick" query optimizer:

ROW_NUMBER() OVER(ORDER BY (SELECT 1)) -- already presented

Other options:

ROW_NUMBER() OVER(ORDER BY 1/0)       -- should not be used
ROW_NUMBER() OVER(ORDER BY @@SPID)
ROW_NUMBER() OVER(ORDER BY DB_ID())
ROW_NUMBER() OVER(ORDER BY USER_ID())

db<>fiddle demo

user_50708790
Related