SQL Server 2012 OFFSET/FETCH NEXT分页示例

原文：http://beyondrelational.com/modules/29/presentations/483/scripts/12983/sql-server-2012-server-side-paging-demo-using-offsetfetch-next.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Apr

 

SQL Server 2005 引入了 ROW_NUMBER()来提供一种容易的分页方式。 SQL Server 2012 添加了另外一种更简单高效的分页方式。

1.  
   /*
2.  
   执行脚本去创建这个表并插入数据。
3.  
   */
4.  
   IF OBJECT_ID('Customers','U') IS NOT NULL
5.  
   DROP TABLE Customers
6.  
    
7.  
   CREATE TABLE Customers (
8.  
   CustomerID INT,
9.  
   CustomerNumber CHAR(4),
10.  
    CustomerName VARCHAR(50),
11.  
    CustomerCity VARCHAR(20) )
12.  
    GO
13.  
     
14.  
    INSERT INTO Customers (
15.  
    CustomerID, CustomerNumber, CustomerName, CustomerCity
16.  
    )
17.  
    SELECT
18.  
    Number,
19.  
    REPLACE(STR(Number, 4), ' ', '0'),
20.  
    'Customer ' + STR(number,6),
21.  
    CHAR(65 + (number % 26)) + '-City'
22.  
    FROM master..spt_values WHERE type = 'p'
23.  
    AND number BETWEEN 0 AND 999
24.  
     
25.  
    /*
26.  
    用ROW_NUMBER()的分页示例- SQL Server 2005/2008 版本。
27.  
    */
28.  
     
29.  
    DECLARE @page INT, @size INT
30.  
    SELECT @page = 3, @size = 10
31.  
     
32.  
    ;WITH cte AS (
33.  
    SELECT TOP (@page * @size)
34.  
    CustomerID,
35.  
    CustomerName,
36.  
    CustomerCity,
37.  
    ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,
38.  
    COUNT(*) OVER(PARTITION BY '') AS Total
39.  
    FROM Customers
40.  
    WHERE CustomerCity IN ('A-City','B-City')
41.  
    ORDER BY CustomerName ASC
42.  
    )
43.  
    SELECT
44.  
    *
45.  
    FROM cte
46.  
    WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size
47.  
    ORDER BY seq
48.  
     
49.  
    /*
50.  
    SQL Server 2012中新添加的分页示例
51.  
    */
52.  
     
53.  
    DECLARE @page INT, @size INT
54.  
    SELECT @page = 3, @size = 10
55.  
     
56.  
    SELECT
57.  
    *,
58.  
    COUNT(*) OVER(PARTITION BY '') AS Total
59.  
    FROM Customers
60.  
    WHERE CustomerCity IN ('A-City','B-City')
61.  
    ORDER BY CustomerID
62.  
    OFFSET (@page -1) * @size ROWS
63.  
    FETCH NEXT @size ROWS ONLY;
64.  
     
65.  
    /*
66.  
    同时执行这两个版本，打开执行计划，你可以注意到在这个例子中显著的性能差异。
67.  
    */