SQL 为什么使用 Select Top 100%？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1622878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why use Select Top 100 Percent?
提问by Ed Schembor
I understand that prior to SQL Server 2005, you could "trick" SQL Server to allow use of an order by in a view definition, by also include TOP 100 PERCENTin the SELECTclause. But I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT... within dynamic SQL statements (used in ADOin ASP.NETapps, etc). Is there any reason for this? Isn't the result the same as notincluding the TOP 100 PERCENT?
我知道在SQL Server 2005之前，您可以“欺骗”SQL Server 以允许在视图定义中使用 order by，也包括TOP 100 PERCENT在SELECT子句中。但是我已经看到我继承的其他代码SELECT TOP 100 PERCENT在动态 SQL 语句中使用...（在ASP.NET应用程序中的ADO中 使用，等等）。这有什么原因吗？结果与不包括 的结果不一样吗？TOP 100 PERCENT
采纳答案by gbn
It was used for "intermediate materialization (Google search)"
它被用于“中间物化（谷歌搜索）”
Good article: Adam Machanic: Exploring the secrets of intermediate materialization
He even raised an MS Connectso it can be done in a cleaner fashion
他甚至提出了一个 MS Connect，所以它可以以更清洁的方式完成
My view is "not inherently bad", but don't use it unless 100% sure. The problem is, it works only at the time you do it and probably not later (patch level, schema, index, row counts etc)...
我的观点是“本质上并不坏”，但除非 100% 确定，否则不要使用它。问题是，它只在你做的时候工作，可能不会在以后（补丁级别、模式、索引、行数等）......
Worked example
工作实例
This may fail because you don't know in which order things are evaluated
这可能会失败，因为您不知道评估事物的顺序
SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100
And this may also fail because
这也可能会失败，因为
SELECT foo
FROM
(SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar
WHERE
CAST(foo AS int) > 100
However, this did not in SQL Server 2000. The inner query is evaluated and spooled:
但是，这在 SQL Server 2000 中没有。内部查询被评估和假脱机：
SELECT foo
FROM
(SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar
WHERE
CAST(foo AS int) > 100
Note, this still works in SQL Server 2005
请注意，这在 SQL Server 2005 中仍然有效
SELECT TOP 2000000000 ... ORDER BY...
回答by Steve Kass
TOP (100) PERCENT is completely meaningless in recent versions of SQL Server, and it (along with the corresponding ORDER BY, in the case of a view definition or derived table) is ignored by the query processor.
TOP (100) PERCENT 在 SQL Server 的最新版本中完全没有意义，它（连同相应的 ORDER BY，在视图定义或派生表的情况下）被查询处理器忽略。
You're correct that once upon a time, it could be used as a trick, but even then it wasn't reliable. Sadly, some of Microsoft's graphical tools put this meaningless clause in.
你是对的，曾几何时，它可以被用作一种技巧，但即便如此它也不可靠。可悲的是，微软的一些图形工具把这个毫无意义的条款放进去了。
As for why this might appear in dynamic SQL, I have no idea. You're correct that there's no reason for it, and the result is the same without it (and again, in the case of a view definition or derived table, without both the TOP and ORDER BY clauses).
至于为什么这会出现在动态 SQL 中，我不知道。你是正确的，没有理由，没有它结果是一样的（同样，在视图定义或派生表的情况下，没有 TOP 和 ORDER BY 子句）。
回答by OMG Ponies
...allow use of an ORDER BY in a view definition.
...允许在视图定义中使用 ORDER BY。
That's not a good idea. A view should never have an ORDER BY defined.
这不是一个好主意。视图永远不应该定义 ORDER BY。
An ORDER BY has an impact on performance - using it a view means that the ORDER BY will turn up in the explain plan. If you have a query where the view is joined to anything in the immediate query, or referenced in an inline view (CTE/subquery factoring) - the ORDER BY is always run prior to the final ORDER BY (assuming it was defined). There's no benefit to ordering rows that aren't the final result set when the query isn't using TOP (or LIMIT for MySQL/Postgres).
ORDER BY 对性能有影响 - 使用它作为视图意味着 ORDER BY 将出现在解释计划中。如果您有一个查询，其中视图与即时查询中的任何内容相连，或在内联视图中引用（CTE/子查询分解） - ORDER BY 始终在最终 ORDER BY 之前运行（假设它已定义）。当查询不使用 TOP（或 MySQL/Postgres 的 LIMIT）时，对不是最终结果集的行进行排序没有任何好处。
Consider:
考虑：
CREATE VIEW my_view AS
SELECT i.item_id,
i.item_description,
it.item_type_description
FROM ITEMS i
JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id
ORDER BY i.item_description
...
...
SELECT t.item_id,
t.item_description,
t.item_type_description
FROM my_view t
ORDER BY t.item_type_description
...is the equivalent to using:
...相当于使用：
SELECT t.item_id,
t.item_description,
t.item_type_description
FROM (SELECT i.item_id,
i.item_description,
it.item_type_description
FROM ITEMS i
JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id
ORDER BY i.item_description) t
ORDER BY t.item_type_description
This is bad because:
这很糟糕，因为：
- The example is ordering the list initially by the item description, and then it's reordered based on the item type description. It's wasted resources in the first sort - running as is does notmean it's running:
ORDER BY item_type_description, item_description - It's not obvious what the view is ordered by due to encapsulation. This does not mean you should create multiple views with different sort orders...
- 该示例最初按项目描述对列表进行排序，然后根据项目类型描述对其进行重新排序。它在第一种情况下浪费了资源 - 按原样运行并不意味着它正在运行：
ORDER BY item_type_description, item_description - 由于封装，视图的排序顺序并不明显。这并不意味着您应该创建具有不同排序顺序的多个视图...
回答by Mitch Wheat
If there is no ORDER BYclause, then TOP 100 PERCENTis redundant. (As you mention, this was the 'trick' with views)
如果没有ORDER BY子句，则TOP 100 PERCENT是多余的。（正如你所提到的，这是视图的“技巧”）
[Hopefully the optimizer will optimize this away.]
[希望优化器会优化它。]
回答by Joel Coehoorn
I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT
我已经看到我继承的其他代码使用 SELECT TOP 100 PERCENT
The reason for this is simple: Enterprise Manager used to try to be helpful and format your code to include this for you. There was no point ever trying to remove it as it didn't really hurt anything and the next time you went to change it EM would insert it again.
原因很简单：企业管理器曾经试图提供帮助并格式化您的代码以将其包含在内。尝试删除它没有任何意义，因为它并没有真正伤害任何东西，下次你去改变它时，EM 会再次插入它。
回答by Peter Radocchia
No reason but indifference, I'd guess.
没有理由，但冷漠，我猜。
Such query strings are usually generated by a graphical query tool. The user joins a few tables, adds a filter, a sort order, and tests the results. Since the user may want to save the query as a view, the tool adds a TOP 100 PERCENT. In this case, though, the user copies the SQL into his code, parameterized the WHERE clause, and hides everything in a data access layer. Out of mind, out of sight.
此类查询字符串通常由图形查询工具生成。用户连接几个表，添加过滤器、排序顺序并测试结果。由于用户可能希望将查询保存为视图，因此该工具添加了 TOP 100 PERCENT。但是，在这种情况下，用户将 SQL 复制到他的代码中，参数化 WHERE 子句，并将所有内容隐藏在数据访问层中。眼不见心不烦。
回答by Lakshminarayanan E
Kindly try the below, Hope it will work for you.
请尝试以下方法，希望对您有用。
SELECT TOP
( SELECT COUNT(foo)
From MyTable
WHERE ISNUMERIC (foo) = 1) *
FROM bar WITH(NOLOCK)
ORDER BY foo
WHERE CAST(foo AS int) > 100
)
回答by John Brewster
Just try this, it explains it pretty much itself. You can't create a view with an ORDER BY except if...
试试这个，它本身就很好地解释了它。您不能使用 ORDER BY 创建视图，除非...
CREATE VIEW v_Test
AS
SELECT name
FROM sysobjects
ORDER BY name
GO
Msg 1033, Level 15, State 1, Procedure TestView, Line 5 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
消息 1033，级别 15，状态 1，过程 TestView，第 5 行 ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP、OFFSET 或 FOR XML。
回答by Fandango68
The error says it all...
错误说明了一切......
Msg 1033, Level 15, State 1, Procedure TestView, Line 5 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
消息 1033，级别 15，状态 1，过程 TestView，第 5 行 ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP、OFFSET 或 FOR XML。
Don't use TOP 100 PERCENT, use TOP n, where N is a number
不要使用TOP 100 PERCENT，使用TOP n，其中 N 是一个数字
The TOP 100 PERCENT (for reasons I don't know) is ignored by SQL Server VIEW (post 2012 versions), but I think MS kept it for syntax reasons. TOP n is better and will work inside a view and sort it the way you want when a view is used initially, but be careful.
SQL Server VIEW（2012 年之后的版本）忽略了 TOP 100 PERCENT（出于我不知道的原因），但我认为 MS 出于语法原因保留了它。TOP n 更好，可以在视图内工作，并在最初使用视图时按照您想要的方式对其进行排序，但要小心。
回答by Lauren Glenn
I would suppose that you can use a variable in the result, but aside from getting the ORDER BY piece in a view, you will not see a benefit by implicitly stating "TOP 100 PERCENT":
我想你可以在结果中使用一个变量，但除了在视图中获取 ORDER BY 部分之外，你不会看到隐式声明“前 100 %”的好处：
declare @t int
set @t=100
select top (@t) percent * from tableOf
