0 votes
1 view
in SQL by (20.3k points)

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!

1 Answer

0 votes
by (40.3k points)

You should avoid using SELECT *. Try to specify columns you actually want even though it may be all of them.

If you are using SQL Server 2005+, then you can use this code: 

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

For SQL Server 2000, Refer to these links:

Efficiently Paging Through Large Result Sets in SQL Server 2000

A More Efficient Method for Paging Through Large Result Sets

Related questions

0 votes
1 answer
+3 votes
1 answer
+1 vote
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...