471,107 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,107 software developers and data experts.

strategies for paging

hello, what are the strategies when designing tables that needs
paging?
in the past i used to use

select top 200 * from table
where id not in (select top 100 id from table)
with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?

or any other advice?

thanks

Sep 28 '07 #1
4 1581
On Fri, 28 Sep 2007 03:12:25 -0700, Nick Chan wrote:
>hello, what are the strategies when designing tables that needs
paging?
in the past i used to use

select top 200 * from table
where id not in (select top 100 id from table)
with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?

or any other advice?
Hi Nick,

With SQL Server 2005, I'd definitely consider the CTE + ROW_NUMBER
approach. The odds are very high that this will perform better than any
other technique - though it's still odds; you'll have to run your own
tests to find out what REALLY is best on your hardware, your data, etc.

Many other techniques are described at the page below; unfortunately it
has not yet been updated for SQL Server 2005 :-((

http://databases.aspfaq.com/database...recordset.html
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sep 28 '07 #2
>or any other advice? <<

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Sep 29 '07 #3
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Even a hobbyist would no better.

Google search, "rogerson", it returns 1,710,000 rows, are you seriously
saying you would pass all 1.7 million rows from the SQL Server to the middle
tier or client only to take the first 10?

Do you not think it makes more resource sense to select just the page of
results you need and pass that back from the SQL Server instead? That would
be 10 rows instead of 1.7 million going across that network link to the
middle tier.
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Not sure what principles you are drawing that statement from but in my
client server training I was taught to do the processing where it is most
appropriate for resource and maintainability reasons.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...
>>or any other advice? <<

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Sep 29 '07 #4
thanks all for the help!

On Sep 29, 3:51 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALIDwrote:
On Fri, 28 Sep 2007 03:12:25 -0700, Nick Chan wrote:
hello, what are the strategies when designing tables that needs
paging?
in the past i used to use
select top 200 * from table
where id not in (select top 100 id from table)
with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?
or any other advice?

Hi Nick,

With SQL Server 2005, I'd definitely consider the CTE + ROW_NUMBER
approach. The odds are very high that this will perform better than any
other technique - though it's still odds; you'll have to run your own
tests to find out what REALLY is best on your hardware, your data, etc.

Many other techniques are described at the page below; unfortunately it
has not yet been updated for SQL Server 2005 :-((

http://databases.aspfaq.com/database...ugh-a-recordse...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Oct 2 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Shawn | last post: by
2 posts views Thread by asad | last post: by
2 posts views Thread by rn5a | last post: by
5 posts views Thread by Donald Adams | last post: by
reply views Thread by origami.takarana | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.