Row Offset in MS SQL Server

Question

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

SELECT * FROM MyTable OFFSET 50 LIMIT 25 to get results 50-74. This construct does not appear to exist in MS SQL.

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

Answer 1

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.

Answer 2

Here is a way of doing paging in SQL Server 2005, to effectivly get your desired results.

Paging in SQL Server 2005

Answer 3

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

Answer 4

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

MS SQL 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

MS SQL 2000

Efficiently Paging Through Large Result Sets in SQL Server 2000

A More Efficient Method for Paging Through Large Result Sets

Answer 5

Yeah, that's a bit crappy in Microsoft SQL Server. 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

Answer 6

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)

Answer 7

You should be careful when using the row_number() OVER (ORDER BY) statement as performane 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 then 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