Row Offset in SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   offset   This solves the pagination

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT TOP 75   FROM MyTable EXCEPT  SELECT TOP 50   FROM MyTable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT TOP 75   FROM MyTable EXCEPT  SELECT TOP 50   FROM MyTable

User · Answer

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  gt   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

User · Answer

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  gt    Rank and ID  gt   Id  For the first time I pass 0 for both  The second time pass 1  amp  14  3rd time pass 2 and 6      The value of the 10th record Rank  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt    Rank and ID  gt   Id  For the first time I pass 0 for both  The second time pass 1  amp  14  3rd time pass 2 and 6      The value of the 10th record Rank  amp  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

User · Answer

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

User · Answer

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  gt   offset   This solves the pagination

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Row Offset in SQL Server

Examples related to sql

Examples related to sql-server