x

1 million rows to be displayed in sets of 100

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 !
more ▼

asked May 21, 2012 at 08:53 AM in Default

fashraf gravatar image

fashraf
428 13 16 20

Just a comment... While the business requirement may be to return all 1 million rows, processing millions of rows, even using some of the efficient operations suggested below, is a bad design. You need to push back on the business requirement and get them to define some mechanisms of filtering the data first. Trust me, no one ever reads millions of rows. They just want to know that the data is there. You should never try to return it to them all at once.
May 21, 2012 at 01:24 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 21, 2012 at 09:07 AM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

(comments are locked)
10|1200 characters needed characters left

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

[1]: http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx
more ▼

answered May 21, 2012 at 09:59 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x46
x11

asked: May 21, 2012 at 08:53 AM

Seen: 1272 times

Last Updated: May 21, 2012 at 01:51 PM