IF OBJECT_ID(‘TESTTBL‘) IS NOT NULL
 DROP TABLE TESTTBL;
GO

CREATE TABLE TESTTBL
(
    empid VARCHAR(10) NOT NULL primary key,
    mgrid VARCHAR(10) NOT NULL,
    qty INT NOT NULL
)

INSERT INTO TESTTBL (empid,mgrid,qty) VALUES
(‘A‘,‘Z‘,300),
(‘B‘,‘X‘,100),
(‘C‘,‘X‘,200),
(‘D‘,‘Y‘,200),
(‘E‘,‘Z‘,250),
(‘F‘,‘Z‘,300),
(‘G‘,‘X‘,100),
(‘H‘,‘Z‘,150),
(‘I‘,‘X‘,250),
(‘J‘,‘Z‘,100),
(‘K‘,‘Y‘,200)

1，  row_number 函数

SELECT empid,qty,ROW_NUMBER() OVER (ORDER BY qty) AS ROWNUM FROM TESTTBL ORDER BY qty

empid qry rownum

B    100    1
G    100    2
J    100    3
H    150    4
K    200    5
C    200    6
D    200    7
E    250    8
I    250    9
F    300    10
A    300    11

2， 基于游标的解决方案
DECLARE @TestRN TABLE (empid VARCHAR(5), qty INT, rn INT);
DECLARE @empid AS VARCHAR(5), @qty as INT, @rn AS INT;
--BEGIN TRAN
    DECLARE rncursor CURSOR FAST_FORWARD FOR
        SELECT empid, qty FROM TESTTBL ORDER BY qty, empid;
    OPEN rncursor;
    SET @rn=0;
    FETCH NEXT FROM rncursor INTO @empid, @qty;
    WHILE @@FETCH_STATUS=0
    BEGIN
        SET @rn=@rn+1;
        INSERT INTO @TestRN (empid,qty,rn) VALUES
        (@empid,@qty,@rn)
        FETCH NEXT FROM rncursor INTO @empid, @qty;
    END
    CLOSE rncursor;
    DEALLOCATE rncusor;
    SELECT empid,qty,rn FROM @TestRN;
--OMMIT TRAN

3， 基于IDENTITY 的解决方案

SELECT empid, qty, IDENTITY(INT, 1, 1) AS rn

INTO @TestRN FROM TESTTBL ORDER BY qty, empid;

SELECT * FROM @TestRN;

DROP TABLE @TestRN;

4, 按行号来实现简单分页

DECLARE @pagesize as INT;
DECLARE @pagenum as INT;
SET @pagesize=3;
SET @pagenum=3;

WITH TEST AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY qty, empid) AS rownum,
    empid, mgrid,qty
    FROM TESTTBL
)
SELECT rownum,empid, mgrid,qty FROM TEST
WHERE rownum > @pagesize * (@pagenum-1)
AND rownum <= @pagesize * @pagenum
ORDER BY rownum;

rownum empid mgrid qty

7    K    Y    200
8    E    Z    250
9    I    X    250

Summary:

在某种程度上，ROW_NUMBER 函数会比 游标、IDENTITY快很多，因为游标和IDENTITY会把结果保存到表中，以备后用，这两种方法会产生大量的I/O.

行号是按指定的逻辑顺序为查询结果集中的行分配的连续整数