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!

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.

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

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

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) 

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

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; 

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

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