Breaking Down values in SQL

Question

I have million of data, if I try to fetch the data in one go. It would take lot of time to get the same. Is it possible that I can break down the value to top 100 values, and then I want to see 101 to 200 values. Can we have that option in SQL to write that query?

Yes. You need to start reading about the SELECT syntax in SQL Server 2008 - you can specify amount of rows to return using TOP, filter data using WHERE and HAVING — Mike, Dec 05 '13 at 08:51
possible duplicate of [How to implement LIMIT with Microsoft SQL Server?](http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server) — Taylan Aydinli, Dec 05 '13 at 08:51
select top 100 * where column- this will give me 100 values...but when i run the same query saying select top 200 * where column, it would show the old values also..whereas in 2nd query I want to see only the values from 101 col to 200 col. How do we write a query for that..can u gie me a eg with a query — user3069018, Dec 05 '13 at 08:54

score 0 · Answer 1 · answered Dec 05 '13 at 08:56

If I understand you correctly it seems like you want to fetch n rows that aren't necessarily at the start or end of any particular order, but rather somewhere in the middle. This is also the basis for "paging" - e.g. fetch N rows, then the next N rows and so on. You could do this with a CTE and ROW_NUMBER() like so:

DECLARE @Start INT = 101;
DECLARE @End INT = 200;

WITH cte AS
(
    SELECT Column1, Column2, Column3, ROW_NUMBER() OVER(ORDER BY <some criteria>) AS RowNumber
    FROM YourTable
)
SELECT Column1, Column2, Column3
FROM cte 
WHERE RowNumber > @Start
AND RowNumber < @End

You could pass in the variables I've declared at the top if you wanted to make this a stored procedure or so on - obviously you would pass 1 and 100 to get the first 100 rows, 101 and 200 to get the next 100 and so on (You might need to adjust the range criteria at the bottom dependant on whether you want exclusive/inclusive/inclusive at one end ranges).

score 0 · Answer 2 · edited May 23 '17 at 12:28

This is generally referred to as pagination or paging. As Bridge pointed out in their response, you can achieve this via ROW_NUMBER(), but there are various other mechanisms as well.

I quite like Remus Rusanu's keyset solution here where its suggested that it's quicker than rowcount based solutions because "...because they (rowcount) always have to count the records to position themselves, instead of seeking straight to the position as keysets can."

I've not seen any hard and fast data that compares the two mechanisms, but maybe that is something you can investigate and report back on in your solution ;)

Breaking Down values in SQL

2 Answers2