为什么使用 Select Top 100%? [英] Why use Select Top 100 Percent?

问题描述

我知道在 SQL Server 2005 之前，您可以欺骗"SQL Server 以允许在视图定义中使用 order by，还包括 TOP 100 PERCENT 在 SELECT 子句中.但是我已经看到我继承的其他代码使用 SELECT TOP 100 PERCENT ... 在动态 SQL 语句中(在 ASP.NET 中的 ADO 中使用)em> 应用程序等).这有什么原因吗?结果是不是与不相同，包括TOP 100 PERCENT?

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 PERCENT in the SELECT clause. But I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT ... within dynamic SQL statements (used in ADO in ASP.NET apps, etc). Is there any reason for this? Isn't the result the same as not including the TOP 100 PERCENT?

推荐答案

It was used for "中间物化(Google 搜索)"

It was used for "intermediate materialization (Google search)"

好文章:Adam Machanic:探索中间物化的秘密

他甚至提出了MS Connect，因此它可以以更清洁的方式完成

He even raised an MS Connect so it can be done in a cleaner fashion

我的观点本质上并不坏"，但除非 100% 确定，否则不要使用它.问题是，它只在你做的时候起作用，可能不会在以后起作用(补丁级别、架构、索引、行数等)......

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)...

这可能会失败，因为您不知道评估事物的顺序

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

这也可能会失败，因为

SELECT foo
FROM
    (SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar
WHERE
    CAST(foo AS int) > 100

然而，这在 SQL Server 2000 中没有.内部查询被评估和假脱机:

However, this did not in SQL Server 2000. The inner query is evaluated and spooled:

SELECT foo
FROM
    (SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar
WHERE
    CAST(foo AS int) > 100

注意，这在 SQL Server 2005 中仍然有效

Note, this still works in SQL Server 2005

SELECT TOP 2000000000 ... ORDER BY...

这篇关于为什么使用 Select Top 100%?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！