1 million rows to be displayed in sets of 100

Question

I have almost 1 mill roes and he size keeps growing. In order not to delay the query I need to display Top 100 followed by 101-200 followed by 201 -300 etc.I am not loading all the data at one time .I need the 100 rows first and then on button click retrieve the next 100 and so on .I have to dispay the data in a single view so paging is not allowed in the grid.Thank you !

Answer 1

0

In SQL 2008, you can use a cte and rownumber to get the records in batches of x records. See the sample below:

DECLARE @start INT = 1
DECLARE @batchSize  INT = 100
;WITH ctePage
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY cp.id ) AS recordno, * FROM dwh.counterpart cp WHERE cp.reportDate = '2012-05-20'
)
SELECT * FROM ctePage WHERE [ctePage].[recordno] BETWEEN @start AND @start + @batchSize

In SQL 2012, you can use OFFSET in the order by clause, without the need for the CTE and rownumber.

answered May 21 '12 at 09:07 AM

Håkan Winther
15k ● 29 ● 35 ● 46

(comments are locked)

Answer 2

0

In addition to @Håkan Winther's answer, you might want to have a read of Dave Ballantyne's blog on the subject.

answered May 21 '12 at 09:59 AM

ThomasRushton ♦
29.1k ● 6 ● 9 ● 36

(comments are locked)

1 million rows to be displayed in sets of 100

Follow this question

Related Questions