[MS SQL] 分頁

整理一下分頁的範例寫法，方便以後查詢參考：


Reference:by 聖殿祭師
分頁的技巧有兩種，一種是直接透過T-SQL，另一種是透過Store Procedure，在這Post出來跟大家分享一下：
T-SQL：
假設Northwind有一個Customer的Table，你需要取回41~50筆的記錄，T-SQL語法該如何作呢？
Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers where CustomerID Not in
(Select top 40 CustomerID from Customers order by Country,CustomerID)
Order by Country,CustomerID

Store Procedure：
出自MSDN Magazine，是別人的智慧
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END

==============我是分隔線=================
SQL2005 新增函數Row_NUMBER 分頁法
參考ms msdn
--使用ROW_NUMBER()新增RowNumber資料欄位

USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 

--指定取出介於50~60列索引的資料列

SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

透過上面的範例可以寫成一個簡單的SP

ALTER PROCEDURE [dbo].[LobbyImages]
 @CourseFaculties int,
 @pageIndex int,
 @pageSize int
AS
BEGIN
select * from(
(select Row_NUMBER() over (order by HomeWorkPefectL4ID )
 as rownum,* from HomeWorkPerfectL4) )as newPerfectL4
 WHERE rownum BETWEEN (@PageIndex-1) * @PageSize + 1 AND @PageIndex * @PageSize
END 

 ==============我是分隔線=================

Pager Control for ASP.NET

這篇也有提供寫法：

sql 2000

sql 2005