Why Use `SELECT TOP 100 PERCENT` in SQL Server: A Necessary Evil or Useless Syntax?

Utilizing SELECT TOP 100 Percent: Insights into Unused Functionality

Traditionally, SELECT TOP 100 PERCENT was employed in view definitions to bypass the restriction against using ORDER BY in such contexts. However, in certain codebases, this functionality appears strangely within dynamic SQL statements. Is there any justification for this inclusion, or does it merely yield the same result as excluding TOP 100 PERCENT?

Surprisingly, SELECT TOP 100 PERCENT did find a unique application in the realm of "intermediate materialization." By leveraging this construct, it was possible to cache query results and optimize subsequent executions. Adam Machanic's article provides an illuminating exploration of this technique.

While the practice of using SELECT TOP 100 PERCENT in this manner is not inherently problematic, it demands extreme caution and should only be employed when absolutely necessary. Its reliance on specific circumstances and environmental factors makes it vulnerable to subtle changes in schema, index structure, or row counts, potentially leading to unexpected and unpredictable behavior.

For clarity, consider the following examples:

Invalidated Example:

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

Invalidated Example:

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

Valid Example (SQL Server 2000 and 2005):

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

Moreover, SELECT TOP 100 PERCENT extends its reach to SQL Server 2005, enabling potentially unlimited results with:

SELECT TOP 2000000000 ... ORDER BY...

The above is the detailed content of Why Use `SELECT TOP 100 PERCENT` in SQL Server: A Necessary Evil or Useless Syntax?. For more information, please follow other related articles on the PHP Chinese website!