SQL Server - Pagination Without Order By Clause

Question

My situation is that a SQL statement which is not predictable, is given to the program and I need to do pagination on top of it. The final SQL statement would be similar to the following one:

SELECT * FROM (*Given SQL Statement*) b 
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

The problem here is that the *Given SQL Statement* is unpredictable. It may or may not contain order by clause. I am not able to change the query result of this SQL Statement and I need to do pagination on it.

I searched for solution on the Internet, but all of them suggested to use an arbitrary column, like primary key, in order by clause. But it will change the original order.

Answer 1

The short answer is that it can't be done, or at least can't be done properly.

The problem is that SQL Server (or any RDBMS) does not and can not guarantee the order of the records returned from a query without an order by clause.
This means that you can't use paging on such queries.
Further more, if you use an order by clause on a column that appears multiple times in your resultset, the order of the result set is still not guaranteed inside groups of values in said column - quick example:

;WITH cte (a, b)
AS
(
    SELECT 1, 'a'
    UNION ALL
    SELECT 1, 'b'
    UNION ALL
    SELECT 2, 'a'
    UNION ALL
    SELECT 2, 'b'
)

SELECT *
FROM cte 
ORDER BY a

Both result sets are valid, and you can't know in advance what will you get:

a   b
-----
1   b
1   a
2   b
2   a

a   b
-----
1   a
1   b
2   a
2   b

(and of course, you might get other sorts)

Answer 2

The problem here is that the *Given SQL Statement" is unpredictable. It may or may not contain order by clause.

your inner query(unpredictable sql statement) should not contain order by,even if it contains,order is not guaranteed.

To get guaranteed order,you have to order by some column.for the results to be deterministic,the ordered column/columns should be unique

Answer 3

Please note: what I'm about to suggest is probably horribly inefficient and should really only be used to help you go back to the project leader and tell them that pagination of an unordered query should not be done. Having said that...

---

From your comments you say you are able to change the SQL statement before it is executed.

You could write the results of the original query to a temporary table, adding row count field to be used for subsequent pagination ordering.

Therefore any original ordering is preserved and you can now paginate.

But of course the reason for needing pagination in the first place is to avoid sending large amounts of data to the client application. Although this does prevent that, you will still be copying data to a temp table which, depending on the row size and count, could be very slow.

You also have the problem that the page size is coming from the client as part of the SQL statement. Parsing the statement to pick that out could be tricky.

Answer 4

As other notified using anyway without using a sorted query will not be safe, But as you know about it and search about it, I can suggest using a query like this (But not recommended as a good way)

;with cte as (
    select *,
        row_number() over (order by (select 0)) rn
    from (
        -- Your query
        ) t
)
select *
from cte
where rn between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

[SQL Fiddle Demo]

Answer 5

I finally found a simple way to do it without any order by on a specific column:

declare @start AS INTEGER = 1, @count AS INTEGER = 5; 

select * from (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS fakeCounter 
FROM (select * from mytable) AS t) AS t2 order by fakeCounter OFFSET @start ROWS 
FETCH NEXT @count ROWS ONLY

where select * from mytable can be any query