strategies for paging

[Nick Chan]

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

[Hugo Kornelis]

Re: strategies for paging

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/how-do-i-page-through-a-recordset.html

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

[--CELKO--]

Re: strategies for paging

>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.

[Tony Rogerson]

Re: strategies for paging

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--" <jcelko212@eart hlink.netwrote in message
news:1191027456 .763592.265890@ d55g2000hsg.goo glegroups.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.
>

[Nick Chan]

Re: strategies for paging

thanks all for the help!

On Sep 29, 3:51 am, Hugo Kornelis
<h...@perFact.R EMOVETHIS.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