Use of TOP 100 PERCENT in SQL Server
Prior to SQL Server 2005, the SELECT TOP 100 PERCENT clause could be used as a trick to force the use of an ORDER BY clause in view definitions. However, its usage extends beyond view definitions in dynamic SQL statements in various applications.
Intermediate Materialization
The primary purpose of SELECT TOP 100 PERCENT in dynamic SQL is for "intermediate materialization." This technique involves creating an intermediate table or query result as a temporary store before the final query execution. By using TOP 100 PERCENT, the temporary store is materialized with all rows from the original query, ensuring the order of the rows is preserved.
Worked Example
Consider the following query:
SELECT foo FROM MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100
This query may fail due to the indeterminate evaluation order between the WHERE and ORDER BY clauses. However, by using TOP 100 PERCENT, the following query will work:
SELECT foo FROM (SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar WHERE CAST(foo AS int) > 100
In SQL Server 2000 and earlier, this technique forced the materialization of the inner query, ensuring the row order was preserved. This same behavior is still present in SQL Server 2005.
However, it's important to note that excessive use of TOP 100 PERCENT can have performance implications, and should only be used when absolutely necessary. It's preferable to rely on explicit table partitioning or stored procedures for row ordering when possible.
The above is the detailed content of When and Why Use `SELECT TOP 100 PERCENT` in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!