How to use OFFSET and Fetch without Order by in SQL Server

Question

I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have same structure.

INSERT INTO @TempTable [some columns]  
select [some columns] from table1 order by col1 
INSERT INTO @TempTable [same columns]
select [some columns] from table2 order by col2
select * from @TempTable OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

This query has syntax error at OFFSET keyword.

Answer 1

There is an even simpler way of providing a dummy ORDER BY clause:

select * from @TempTable ORDER BY(SELECT NULL) OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

Answer 2

You cannot avoid using the required syntax of a ORDER BY with OFFSET and FETCH.

It is however possible to disassociate the ORDER BY clause that you must provide in order to perform paging from the natural table order created by the record insert process.

Using the solution below you do not have to make any changes to the underlying table either

Select 0 as TempSort, T.* From MyTable T ORDER BY TempSort OFFSET 2 ROWS FETCH NEXT 3 ROWS