Sql server 2005帶來的分頁便利

select threadid from (select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads) as T where T.Pos > 100000 and T.Pos < 100030

===========================================

如果里面的這個表cs_threads數據量超大，比如，幾億條記錄，那這個方法應該是有問題的

因為，select threadid from ( select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads ) as T where T.Pos > 100000 and T.Pos < 100030 這個語句是把select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads它全部取出來，然后在SQL的外面進行分頁的，沒在SQL2005上測試過，因為原先在ORACLE上這樣的寫法是不好的，ORACLE中這樣寫比較好：select threadid from ( select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads a where a.pos<100030 ) as T where T.Pos > 100000

出處：blog.joycode.com/dotey/archive/2006/01/16/70493.aspx