ROW_NUMBER 没有 ORDER BY [英] ROW_NUMBER Without ORDER BY

问题描述

我必须在现有查询中添加行号，以便跟踪已添加到 Redis 中的数据量.如果我的查询失败，那么我可以从在其他表中更新的那行开始.

查询从表中第 1000 行开始获取数据

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (Order by (select 1)) as rn ) as X where rn >1000

查询工作正常.如果我可以在不使用 order by 的情况下获得行号.

这里的select 1是什么?

查询是优化的还是我可以通过其他方式来完成.请提供更好的解决方案.

解决方案

无需担心在 ORDER BY 表达式中指定常量.以下引用自

<块引用>

在Index Scan迭代器的属性中观察Ordered属性为 False，意味着迭代器不需要返回索引键顺序的数据

<小时>

上面的意思是当你使用常量排序时不执行.我强烈建议阅读本书，因为Itzik Ben-Gan 深入描述了窗口函数的工作原理以及如何在使用它们时优化各种情况.

I've to add row number in my existing query so that I can track how much data has been added into Redis. If my query failed so I can start from that row no which is updated in other table.

Query to get data start after 1000 row from table

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (Order by (select 1)) as rn ) as X where rn > 1000

Query is working fine. If any way that I can get the row no without using order by.

What is select 1 here?

Is the query optimized or I can do it by other ways. Please provide the better solution.

解决方案

There is no need to worry about specifying constant in the ORDER BY expression. The following is quoted from the Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions written by Itzik Ben-Gan (it was available for free download from Microsoft free e-books site):

As mentioned, a window order clause is mandatory, and SQL Server doesn’t allow the ordering to be based on a constant—for example, ORDER BY NULL. But surprisingly, when passing an expression based on a subquery that returns a constant—for example, ORDER BY (SELECT NULL)—SQL Server will accept it. At the same time, the optimizer un-nests, or expands, the expression and realizes that the ordering is the same for all rows. Therefore, it removes the ordering requirement from the input data. Here’s a complete query demonstrating this technique:

SELECT actid, tranid, val,
 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM dbo.Transactions;

Observe in the properties of the Index Scan iterator that the Ordered property is False, meaning that the iterator is not required to return the data in index key order

---

The above means that when you are using constant ordering is not performed. I will strongly recommend to read the book as Itzik Ben-Gan describes in depth how the window functions are working and how to optimize various of cases when they are used.

这篇关于ROW_NUMBER 没有 ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！