SQLServer中ROW_NUMBER（）OVER基本用法

SQLServer中ROW_NUMBER（）OVER基本⽤法
1、不能排序法
SELECT TOP10*
FROM table1
WHERE id NOT IN (
SELECT TOP开始的位置 id
FROM table1
)
2、SQL 2000 临时表法
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
3、SQL 2005/2008 Row_Number法
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
4、SQL SERVER 2012以后 OFFSET/FETCH NEXT法
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS FETCH NEXT50 ROWS ONLY;
语法说明：
ORDER BY ORDER_BY_EXPRESSION
[ COLLATE COLLATION_NAME ]
[ ASC | DESC ]
[ ,...N ]
[ <OFFSET_FETCH> ]
<OFFSET_FETCH> ::=
{
OFFSET { INTEGER_CONSTANT | OFFSET_ROW_COUNT_EXPRESSION } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {INTEGER_CONSTANT | FETCH_ROW_COUNT_EXPRESSION } { ROW | ROWS } ONLY
]
}
--FIRST 和 NEXT 是同义词，是为了与 ANSI 兼容⽽提供的。

--ROW 和 ROWS 是同义词，是为了与 ANSI 兼容⽽提供的。

对⽐分析：
1~100⾏5001~5100⾏9900~10000⾏估计⾏数
1~100⾏5001~5100⾏9900~10000⾏估计⾏数OFFSET FETCH开销占⽐49%84%90%100 ROW_NUMBER开销占⽐51%16%10%9
ROW_NUMBER 在编译内存，CPU ⽐ OFFSET FETCH 多。

上⾯统计中：OFFSET FETCH 查询的记录在表中越靠后，开销反⽽更⼤，⽽这个的估计⾏数是准确的。