SQLServer2005中实现分页查询数据

WITH, ROW_NUMBER and OVER这三个关键字是SQL Server2005中新增加的，通过这些关键字来实现分页查询非常简单，下面仅实现一个简单的例子：

With Cust AS
    ( SELECT CustomerID, CompanyName,
    ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
    FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

将上述的代码封装到的存储过程中：

CREATE PROC GetCustomersByPage

@PageSize int, @PageNumber int 

AS 

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

END

更详细的内容参考http://www.codeproject.com/KB/database/PagingResults.aspx