使用Sql Server2005中新添加的row_number()和rank()返回行号或排名

row_number()和rank()都可以返回行号列

语法:
RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > ) row_number ( )    
OVER ( [ < partition_by_clause > ] < order_by_clause >

参数:
< partition_by_clause > 将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >确定将 RANK 值应用于分区中的行时所基于的顺序。有关详细信息，请参阅 ORDER BY 子句 (Transact-SQL)。

例子:

SELECT 
    cate_id , news_id , rank() over(order by cate_id desc) as row_num
  FROM news

cate_id    news_id     row_num
-------- ----------- ------------
24           11482       1
24           11502       2
23           11503       3
23           11486       4
23           11487       5
22           11492       6
22           11493       7
22           11494       8
21           11495       9
21           11496       10

SELECT 
    cate_id , news_id , rank() over(order by cate_id desc) as row_num
  FROM news

cate_id    news_id     row_num
-------- ----------- ------------
24           11482       1
24           11502       1
23           11503       3    前面两行所以为3
23           11486       3    orderby在cate_id上,cate_id=23的行号都为3
23           11487       3
22           11492       6
22           11493       6
22           11494       6
21           11495       9   前面9行 所以为9
21           11496       9

row_number和rank的主要区别是rank返回的是唯一的行号,而rank返回的不一定是唯一值,它更具在orderby列上具有相同的值会返回一样的行号,某行的行号是相关行之前的行数加1。


set rowcount n
使 SQL Server 在返回指定的行数之后停止处理查询。(限制返回的行数)
注意:在 SQL Server 的下一个版本中，使用 SET ROWCOUNT 将不会影响 DELETE、INSERT 和 UPDATE 语句。在新的开发工作中，避免将 SET ROWCOUNT 语句与 DELETE、INSERT 和 UPDATE 语句一起使用，并计划修改当前使用该语句的应用程序。另外，对于当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句，建议您使用 TOP 语法重写它们。有关详细信息，请参阅 DELETE (Transact-SQL)、INSERT (Transact-SQL) 或 UPDATE (Transact-SQL)。

set rowcount 5;
SELECT 
    cate_id , news_id , rank() over(order by cate_id desc) as row_num
  FROM news

cate_id    news_id     row_num
-------- ----------- ------------
24           11482       1
24           11502       2
23           11503       3
23           11486       4
23           11487       5

要将此选项设置为 off 以便返回所有的行，请将 SET ROWCOUNT 指定为 0。

TOP 和 SET ROWCOUNT
限制结果集大小的另一种方法是在执行一个语句之前执行 SET ROWCOUNT n 语句。SET ROWCOUNT 与 TOP 的不同之处体现在下列方面：

SET ROWCOUNT 限制适用于计算 ORDER BY 后在结果集中生成行。如果指定了 ORDER BY，SELECT 语句将在从根据指定的 ORDER BY 分类进行排序的某个值集中选择 n 行后结束。

TOP 子句适用于指定了该子句的单个 SELECT 语句。SET ROWCOUNT 将一直有效，直到执行另一个 SET ROWCOUNT 语句，例如 SET ROWCOUNT 0 将关闭该选项。

重要事项： 
使用 SET ROWCOUNT 不会影响 SQL Server 下一版本中的 DELETE、INSERT 和 UPDATE 语句。应避免在新的开发工作中将 SET ROWCOUNT 与 DELETE、INSERT 和 UPDATE 语句一起使用，并计划修改当前使用它的应用程序。建议重写当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句以使用 TOP。
 

尽管 SET ROWCOUNT 对于 SELECT 语句的影响仍未改变，但将 TOP 与 SELECT 一起使用要优于使用 SET ROWCOUNT，原因如下：

当 SELECT、INSERT、UPDATE 和 DELETE 语句影响指定的行数时，SET ROWCOUNT 将导致这些语句中的大多数停止处理。此行为也适用于内部触发器的激发。

作为 SELECT 语句的一部分，查询优化器可以使用 TOP 子句中 expression 的值作为生成查询的执行计划的一部分。由于 SET ROWCOUNT 在执行查询的语句外使用，因此它的值不能用于生成查询的查询计划。