Why Use 'SELECT TOP 100 PERCENT' in SQL Queries?

Why Use "Select Top 100 Percent"?

Prior to SQL Server 2005, developers used the "Select Top 100 Percent" trick to enable the use of an order by clause in a view definition. However, beyond this purpose, some code inherited today still includes "Select Top 100 Percent" in dynamic SQL statements used within ADO in ASP.NET applications.

The Question: Is there a reason for using "Top 100 Percent" in these scenarios?

The Answer: Intermediate Materialization

According to experts like Adam Machanic, "Select Top 100 Percent" was used for "intermediate materialization." Here's why:

In SQL Server, queries can be evaluated in different orders depending on the circumstances. For instance, the following two queries may produce different results due to the ambiguity in evaluation order:

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

However, before SQL Server 2005, enclosing the inner query in "Select Top 100 Percent" forced the inner query to be spooled, resulting in consistent evaluation order:

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

Cautionary Note

While this technique was acceptable in the past, it is generally advised to avoid using "Select Top 100 Percent" in dynamic SQL. The results may not be consistent due to changes in patch levels, schema, indexes, or row counts.

The above is the detailed content of Why Use 'SELECT TOP 100 PERCENT' in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!