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
7. CREATE TABLE Customers (
8. CustomerID INT,
9. CustomerNumber CHAR(4),
10. CustomerName VARCHAR(50),
11. CustomerCity VARCHAR(20) )
12. GO
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
25. /*
26. 用ROW_NUMBER()的分页示例- SQL Server 2005/2008 版本。
27. */
29. DECLARE @page INT, @size INT
30. SELECT @page = 3, @size = 10
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
49. /*
50. SQL Server 2012中新添加的分页示例
51. */
53. DECLARE @page INT, @size INT
54. SELECT @page = 3, @size = 10
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;
65. /*
66. 同时执行这两个版本，打开执行计划，你可以注意到在这个例子中显著的性能差异。
67. */