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!
This question is related to
sql
sql-server
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
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.
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.
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
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
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.
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)
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
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
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.
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
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 ;)
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
SELECT TOP 75 * FROM MyTable
EXCEPT
SELECT TOP 50 * FROM MyTable
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.
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)
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
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 ;)
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
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
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.
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.
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
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.
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
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
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.
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.
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
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.
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.
Source: Stackoverflow.com