ROW_NUMBER Without ORDER BY

Question

I need to add a line number to my existing query so that I can track how much data has been added to Redis. If my query failed, so I can start with this line no, which is updated in another table.

Query to run data after 1000 rows from a table

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

The request is working fine. If any way I can get the string without using order.

What is select 1 here?

Whether the request is optimized or I can do it in other ways. Please provide the best solution.

Answer 1

No need to worry about specifying a constant in an ORDER BY . The following is Microsoft SQL Server 2012 High-performance T-SQL using window functions written by Itzik Ben-Gan (it was available for free download from the Microsoft free e-book site):

As already mentioned, the proposal to order a window is mandatory, and SQL Server does not allow ordering based on a constant, for example, ORDER BY NULL. But it is surprising that when passing an expression based on a subquery that returns a constant, for example ORDER BY (SELECT NULL) -SQL Server will accept it. At the same time, the optimizer un-sockets or expands, expressing and realizing that the order is the same for all strings. Therefore, it removes the ordering requirement from the input. Here is a complete query demonstrating this technique:

 SELECT actid, tranid, val, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM dbo.Transactions; 

Observe in the properties of the scan index iterator that the ordered property is False, which means that the iterator does not need to return data in index key order

---

The foregoing means that when using a standing order is not fulfilled. I highly recommend reading the book, as Itzik Ben-Gan describes in detail how window functions work and how to optimize various cases when they are used.

Answer 2

Try just order by 1 . Read the error message. Then restore order by (select 1) . Understand that the person who wrote this at some point read the error message, and then decided that the right thing was to trick the system so as not to raise an error, and not understand the basic truth that this error was trying to warn.

Tables do not have an inline order. If you want to get some kind of order form that you can rely on, you should provide sufficient deterministic expression (s) to any ORDER BY so that each line is uniquely identified and ordered.

Anything else, including tricking the system so as not to emit errors, hopes that the system will do something reasonable without using the tools provided to you to make sure that it is doing something reasonable - a clearly stated ORDER BY .

Answer 3

You can use any literal value

ex

 order by (select 0) order by (select null) order by (select 'test') 

etc.

See this for more information. https://madhivanan.wordpress.com/2017/03/31/row_number-function-with-no-specific-order/