Posted by Hugo Kornelis on 09/28/07 19:51

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

[Back to original message]