T-SQL 分頁從資料庫就做好 Row_number()

SQL 2005之後才有的Row_number()這個Function可以用來做分頁. 從一些參考網站所做的效能評比中,也算是效能數一數二的做法.

以下就是說明Row_number()的基本做法:
select a.UserName,a.UserID
from
(select
  row_number() over (order by UserID) as UID,
  UserName,
  UserID
 from UserDataInfo
 where bmi>20) as a
where a.UID between @SP and @EP

這段語法就是關鍵所在,正常來說,上面的語法只有紅字部份,帶出所有bmi>20的人員,一次把資料全部回傳,但這裡卻多了一段藍字在裡面,Row_Number()就是將符合條件的結果,再從1開始,依序給予編號,所以回傳的結果就會是
UID     UserName     UserID
1          Jeff                   A0001
2          Jerry              C0014
3          Judy               C0096
4          Mark               D0002
5          Jason               D0010
6          Rober               D0011
7          Martin            D0022
所以黑色部份的語法,就依這個的查詢結果,取出UID介於幾號到幾號之間. 所以就可以在DataAccess端,依據設定的PageSize,來算頁次,再依目前的頁次與PageSize去帶回所需顯示的資料範圍.

如果不分頁的話,一次載入上萬筆資料是很可怕的,尤其是透過2M/256K的頻寬,這是ERP系統,不是P2P下載平台,使用者不可能等,如果用分頁方式,就只會一次傳回指定的筆數,大幅的減少網路的負擔,而且這是在DB就做好的動作,所以從DataBase到WebService這段的網路問題也解決了.

這做法也不是沒有缺點,例如在換頁過程中,突然又有幾筆資料符合或不符合了,其順序就會異動,就有可能剛好在換頁時,某筆資料會沒看到,可能跳到前頁或下頁去了. 這部份就看各家使用者了.

以上文章節錄自：Jeff隨手記 http://www.dotblogs.com.tw/jeff-yeh/archive/2008/04/08/2614.aspx