Menu
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.
942
The row_number() window function can be used without order by in over to arbitrarily assign a unique value to each row.
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY. The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required.” So apparently the window order clause is mandatory for the ROW_NUMBER function in SQL Server.
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY . If you do not want to order the result set and still want to generate the row numbers, then you can use a dummy sub query column inside the ORDER BY clause.
Row Number Without Using Order By In SQL Server. You might have already known that using ROW_NUMBER() function is used to generate sequential row numbers for the result set returned from the select query. You have to use ORDER BY clause along with ROW_NUMBER() to generate row numbers.
Because the ROW_NUMBER () is an order sensitive function, the ORDER BY clause is required. Finally, each row in each partition is assigned a sequential integer number called a row number. The row number is reset whenever the partition boundary is crossed.
If you try to use the ROW_NUMBER () function without ORDER BY clause, you will get an error as seen below. The function 'ROW_NUMBER' must have an OVER clause with ORDER BY. If you do not want to order the result set and still want to generate the row numbers, then you can use a dummy sub query column inside the ORDER BY clause.
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY. So nope, doesn’t work. But can we get around it? Ok, so you can do it, but it takes some work.
There is no need to worry about specifying constant in the ORDER BY expression. The following is quoted from the Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions written by Itzik Ben-Gan (it was available for free download from Microsoft free e-books site):
As mentioned, a window order clause is mandatory, and SQL Server doesn’t allow the ordering to be based on a constant—for example, ORDER BY NULL. But surprisingly, 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-nests, or expands, the expression and realizes that the ordering is the same for all rows. Therefore, it removes the ordering requirement from the input data. Here’s 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 Index Scan iterator that the Ordered property is False, meaning that the iterator is not required to return the data in index key order
The above means that when you are using constant ordering is not performed. I will strongly recommend to read the book as Itzik Ben-Gan describes in depth how the window functions are working and how to optimize various of cases when they are used.
165
Try just order by 1. Read the error message. Then reinstate the order by (select 1). Realise that whoever wrote this has, at some point, read the error message and then decided that the right thing to do is to trick the system into not raising an error rather than realising the fundamental truth that the error was trying to alert them to.
Tables have no inherent order. If you want some form of ordering that you can rely upon, it's up to you to provide enough deterministic expression(s) to any ORDER BY clause such that each row is uniquely identified and ordered.
Anything else, including tricking the system into not emitting errors, is hoping that the system will do something sensible without using the tools provided to you to ensure that it does something sensible - a well specified ORDER BY clause.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
