今天无聊和朋友讨论分页，发现网上好多都是错的。网上经常查到的那个Top Not in 或者Max 大部分都不实用，很多都忽略了Order和性能问题。为此上网查了查，顺带把2000和2012版本的也补上了。
先说说网上常见SQL的错误或者说局限问题
|
1
2
3
4
5 |
select top
10 * from table1where id not in( select
top 开始的位置 id from
table1) |
这样的确是可以取到分页数据，但是这是默认排序的，如果要按其中一列排序呢？那order by 加在哪里呢？里外都加，显然不行，外面的Order不起作用，只能嵌套，Oh my god,编程三个Select了，这效率。
为了好用效率高，总体思路还是老老实实的用RowNumber解决，但是SQL2000没有RowNumber，其实我们可以通过临时表自增列搞定，不多说，上例子。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 |
DECLARE @Start INTDECLARE @End INTSELECT @Start = 14000,@End
= 14050CREATE TABLE #employees (RowNumber INT
IDENTITY(1,1),LastName VARCHAR(100),FirstName VARCHAR(100),EmailAddress VARCHAR(100))INSERT
INTO #employees (LastName, FirstName, EmailAddress)SELECT
LastName, FirstName, EmailAddress FROM Employee ORDER BY LastName, FirstName, EmailAddressSELECT
LastName, FirstName, EmailAddress FROM #employeesWHERE RowNumber > @Start AND
RowNumber <= @EndDROP TABLE #employeesGO |
|
1
2
3
4
5
6
7
8
9
10
11
12 |
DECLARE @Start INTDECLARE @End INTSELECT @Start = 14000,@End
= 14050SELECT LastName, FirstName, EmailAddressFROM (SELECT LastName, FirstName, EmailAddress,ROW_NUMBER() OVER (ORDER
BY LastName, FirstName, EmailAddress) AS
RowNumberFROM Employee) EmployeePageWHERE RowNumber > @Start AND
RowNumber <= @EndORDER BY LastName, FirstName, EmailAddressGO |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
DECLARE @Start INTDECLARE @End INTSELECT @Start = 14000,@End
= 14050;WITH EmployeePage AS(SELECT
LastName, FirstName, EmailAddress,ROW_NUMBER() OVER (ORDER
BY LastName, FirstName, EmailAddress) AS
RowNumberFROM Employee)SELECT LastName, FirstName, EmailAddressFROM EmployeePageWHERE RowNumber > @Start AND
RowNumber <= @EndORDER BY LastName, FirstName, EmailAddressGO |
|
1
2
3
4
5 |
SELECT LastName, FirstName, EmailAddressFROM EmployeeORDER BY LastName, FirstName, EmailAddressOFFSET 14000 ROWSFETCH NEXT 50 ROWS ONLY; |
最后说下，根据老外的文章，在2012里，如果前面加上TOP（50），那么执行计划就会少读很多行数据（读的精准了），提高性能。但是鉴于本人手头没2012也无法测试。至少在2008R2上加不加TOP执行计划都一样。
原文：http://www.cnblogs.com/ebread/p/SQLServer.html