原文地址:http://www.cnblogs.com/ebread/p/SQLServer.html
今天无聊和朋友讨论分页,发现网上好多都是错的。网上经常查到的那个Top Not in 或者Max 大部分都不实用,很多都忽略了Order和性能问题。为此上网查了查,顺带把2000和2012版本的也补上了。
先说说网上常见SQL的错误或者说局限问题
1
2
3
4
5
|
select top 10
* from table1 where id not in ( select top 开始的位置
id from table1) |
这样的确是可以取到分页数据,但是这是默认排序的,如果要按其中一列排序呢?那order by 加在哪里呢?里外都加,显然不行,外面的Order不起作用,只能嵌套,Oh my god,编程三个Select了,这效率。
为了好用效率高,总体思路还是老老实实的用RowNumber解决,但是SQL2000没有RowNumber,其实我们可以通过临时表自增列搞定,不多说,上例子。
SQL 2000 用临时表解决,通过在临时表中增加自增列解决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 INT DECLARE @ End INT SELECT @Start
= 14000,@ End =
14050 CREATE 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, EmailAddress SELECT LastName,
FirstName, EmailAddress FROM #employees WHERE RowNumber
> @Start AND RowNumber
<= @ End DROP TABLE #employees GO |
SQL 2005/2008 由于支持了Row_Number于是通过派生表的方式解决(两个嵌套)
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @Start INT DECLARE @ End INT SELECT @Start
= 14000,@ End =
14050 SELECT LastName,
FirstName, EmailAddress FROM ( SELECT LastName,
FirstName, EmailAddress, ROW_NUMBER()
OVER ( ORDER BY LastName,
FirstName, EmailAddress) AS RowNumber FROM Employee)
EmployeePage WHERE RowNumber
> @Start AND RowNumber
<= @ End ORDER BY LastName,
FirstName, EmailAddress GO |
SQL 2005/2008 或者用CTE的方式实现,和派生表一样,就是好看点,执行计划都一样。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DECLARE @Start INT DECLARE @ End INT SELECT @Start
= 14000,@ End =
14050; WITH EmployeePage AS ( SELECT LastName,
FirstName, EmailAddress, ROW_NUMBER()
OVER ( ORDER BY LastName,
FirstName, EmailAddress) AS RowNumber FROM Employee) SELECT LastName,
FirstName, EmailAddress FROM EmployeePage WHERE RowNumber
> @Start AND RowNumber
<= @ End ORDER BY LastName,
FirstName, EmailAddress GO |
SQL SERVER 2012 比较给力支持了OFFSET,于是一个Select结束战斗
1
2
3
4
5
|
SELECT LastName,
FirstName, EmailAddress FROM Employee ORDER BY LastName,
FirstName, EmailAddress OFFSET
14000 ROWS FETCH NEXT 50 ROWS ONLY ; |
最后说下,根据老外的文章,在2012里,如果前面加上TOP(50),那么执行计划就会少读很多行数据(读的精准了),提高性能。但是鉴于本人手头没2012也无法测试。至少在2008R2上加不加TOP执行计划都一样。