Row Offset in SQL Server

Is there any way in SQL Server to get the results starting at a given offset? For example, in another type of SQL database, it's possible to do:

SELECT * FROM MyTable OFFSET 50 LIMIT 25 

to get results 51-75. This construct does not appear to exist in SQL Server.

How can I accomplish this without loading all the rows I don't care about? Thanks!

17 Answers

I would avoid using SELECT *. Specify columns you actually want even though it may be all of them.

SQL Server 2005+

SELECT col1, col2 FROM ( SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow 

SQL Server 2000

Efficiently Paging Through Large Result Sets in SQL Server 2000

A More Efficient Method for Paging Through Large Result Sets

If you will be processing all pages in order then simply remembering the last key value seen on the previous page and using TOP (25) ... WHERE Key > @last_key ORDER BY Key can be the best performing method if suitable indexes exist to allow this to be seeked efficiently - or an API cursor if they don't.

For selecting an arbitary page the best solution for SQL Server 2005 - 2008 R2 is probably ROW_NUMBER and BETWEEN

For SQL Server 2012+ you can use the enhanced ORDER BY clause for this need.

SELECT * FROM MyTable ORDER BY OrderingColumn ASC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

Though it remains to be seen how well performing this option will be.

This is one way (SQL2000)

SELECT * FROM ( SELECT TOP (@pageSize) * FROM ( SELECT TOP (@pageNumber * @pageSize) * FROM tableName ORDER BY columnName ASC ) AS t1 ORDER BY columnName DESC ) AS t2 ORDER BY columnName ASC 

and this is another way (SQL 2005)

;WITH results AS ( SELECT rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC ) , * FROM tableName ) SELECT * FROM results WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize 

You can use ROW_NUMBER() function to get what you want:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t WHERE RowNr BETWEEN 10 AND 20 

There is OFFSET .. FETCH in SQL Server 2012, but you will need to specify an ORDER BY column.

If you really don't have any explicit column that you could pass as an ORDER BY column (as others have suggested), then you can use this trick:

SELECT * FROM MyTable ORDER BY @@VERSION OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

... or

SELECT * FROM MyTable ORDER BY (SELECT 0) OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY 

We're using it in jOOQ when users do not explicitly specify an order. This will then produce pretty random ordering without any additional costs.

For tables with more and large data columns, I prefer:

SELECT tablename.col1, tablename.col2, tablename.col3, ... FROM ( ( SELECT col1 FROM ( SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum FROM tablename WHERE ([CONDITION]) ) AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT] ) AS T2 INNER JOIN tablename ON T2.col1=tablename.col1 ); 

-

[CONDITION] can contain any WHERE clause for searching. [OFFSET] specifies the start, [LIMIT] the maximum results. 

It has much better performance on tables with large data like BLOBs, because the ROW_NUMBER function only has to look through one column, and only the matching rows are returned with all columns.

See my select for paginator

SELECT TOP @limit * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM ( -- YOU SELECT HERE SELECT * FROM mytable ) myquery ) paginator WHERE offset > @offset 

This solves the pagination ;)

SELECT TOP 75 * FROM MyTable EXCEPT SELECT TOP 50 * FROM MyTable 

Depending on your version ou cannot do it directly, but you could do something hacky like

select top 25 * from ( select top 75 * from table order by field asc ) a order by field desc 

where 'field' is the key.

Following will display 25 records excluding first 50 records works in SQL Server 2012.

SELECT * FROM MyTable ORDER BY ID OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; 

you can replace ID as your requirement

You should be careful when using the ROW_NUMBER() OVER (ORDER BY) statement as performance is quite poor. Same goes for using Common Table Expressions with ROW_NUMBER() that is even worse. I'm using the following snippet that has proven to be slightly faster than using a table variable with an identity to provide the page number.

DECLARE @Offset INT = 120000 DECLARE @Limit INT = 10 DECLARE @ROWCOUNT INT = @Offset+@Limit SET ROWCOUNT @ROWCOUNT SELECT * FROM MyTable INTO #ResultSet WHERE MyTable.Type = 1 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY SortConst ASC) As RowNumber FROM ( SELECT *, 1 As SortConst FROM #ResultSet ) AS ResultSet ) AS Page WHERE RowNumber BETWEEN @Offset AND @ROWCOUNT DROP TABLE #ResultSet 

I use this technique for pagination. I do not fetch all the rows. For example, if my page needs to display the top 100 rows I fetch only the 100 with where clause. The output of the SQL should have a unique key.

The table has the following:

ID, KeyId, Rank 

The same rank will be assigned for more than one KeyId.

SQL is select top 2 * from Table1 where Rank >= @Rank and ID > @Id

For the first time I pass 0 for both. The second time pass 1 & 14. 3rd time pass 2 and 6....

The value of the 10th record Rank & Id is passed to the next

11 21 1 14 22 1 7 11 1 6 19 2 12 31 2 13 18 2 

This will have the least stress on the system

In SqlServer2005 you can do the following:

DECLARE @Limit INT DECLARE @Offset INT SET @Offset = 120000 SET @Limit = 10 SELECT * FROM ( SELECT row_number() OVER (ORDER BY column) AS rownum, column2, column3, .... columnX FROM table ) AS A WHERE A.rownum BETWEEN (@Offset) AND (@Offset + @Limit-1) 

Best way to do it without wasting time to order records is like this :

select 0 as tmp,Column1 from Table1 Order by tmp OFFSET 5000000 ROWS FETCH NEXT 50 ROWS ONLY 

it takes less than one second!
best solution for large tables.

With SQL Server 2012 (11.x) and later and Azure SQL Database, you can also have "fetch_row_count_expression", you can also have ORDER BY clause along with this.

USE AdventureWorks2012; GO -- Specifying variables for OFFSET and FETCH values DECLARE @skip int = 0 , @take int = 8; SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID ASC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY; 

Note OFFSET Specifies the number of rows to skip before it starts to return rows from the query expression. It is NOT the starting row number. So, it has to be 0 to include first record.

I've been searching for this answer for a while now (for generic queries) and found out another way of doing it on SQL Server 2000+ using ROWCOUNT and cursors and without TOP or any temporary table.

Using the SET ROWCOUNT [OFFSET+LIMIT] you can limit the results, and with cursors, go directly to the row you wish, then loop 'till the end.

So your query would be like this:

SET ROWCOUNT 75 -- (50 + 25) DECLARE MyCursor SCROLL CURSOR FOR SELECT * FROM pessoas OPEN MyCursor FETCH ABSOLUTE 50 FROM MyCursor -- OFFSET WHILE @@FETCH_STATUS = 0 BEGIN FETCH next FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor SET ROWCOUNT 0 

Method 1:

Ordering seem to matter here

Bringing limit before offset seems to work.

SELECT * FROM MyTable LIMIT 25 OFFSET 50 

Method 2:

Alternatively, you can use limit only
LIMIT takes two values
1st: Offset value
2nd: No of rows to be displayed

SELECT *
FROM MyTable
LIMIT (OffsetValue), (NoOfRows)

SELECT * FROM MyTable LIMIT 50, 25