在一些其他数据库(例如DB2或具有ROWNUM的Oracle)中,我可以在排序函数的OVER()子句中省略ORDER BY子句.例如：
ROW_NUMBER() OVER()
当与有序派生表一起使用时,这是特别有用的,例如：
SELECT t.*,ROW_NUMBER() OVER()
FROM (
SELECT ...
ORDER BY
) t
如何在sql Server中仿效？我发现人们使用this trick,但是这是错误的,因为它将对派生表中的顺序具有非确定性：
-- This order here ---------------------vvvvvvvv
SELECT t.*,ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (
SELECT TOP 100 PERCENT ...
-- vvvvv ----redefines this order here
ORDER BY
) t
一个具体的例子(从SQLFiddle可以看出)：
SELECT v,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN FROM ( SELECT TOP 100 PERCENT 1 UNION ALL SELECT TOP 100 PERCENT 2 UNION ALL SELECT TOP 100 PERCENT 3 UNION ALL SELECT TOP 100 PERCENT 4 -- This descending order is not maintained in the outer query ORDER BY 1 DESC ) t(v)
另外,我不能使用派生表中的任何表达式来重现我的情况下的ORDER BY子句,因为派生表可能不可用,因为它可能由一些外部逻辑提供.
那我该怎么办呢我可以做吗
解决方法
Row_Number()OVER(ORDER BY(SELECT 1)))技巧不应被视为避免更改底层数据顺序的一种方式.只是避免使服务器执行额外的和不需要的排序(它可能仍然执行排序,但是与列排序相比,可能会花费最小的金额).
sql Server中的所有查询绝对必须在最外层查询中具有ORDER BY子句,以便以有保证的方式可靠地排序结果.
关系数据库中不存在“保留原始顺序”的概念.始终将表和查询视为无序的,除非在最外层查询中指定了ORDER BY子句.
您可以尝试相同的无序查询100,000次,并始终以相同的顺序接收它,因此相信您可以依赖于所述排序.但这是一个错误,因为有一天会有变化,不会有你期望的顺序.一个例子是当数据库升级到新版本的sql Server时 – 这引起了许多查询以更改其排序.但它不一定是那么大的变化.与添加或删除索引一样,可能会导致差异.还有更多：安装Service Pack.分隔一张桌子创建包含该表的索引视图.达到一个临时点,而不是寻找扫描.等等.
不要依赖结果来排序,除非你说“Server,ORDER BY”.
