Why use Select Top 100 Percent?

by

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

Good article: Adam Machanic: Exploring the secrets of intermediate materialization

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

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

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:

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

SELECT TOP 2000000000 ... ORDER BY...

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. How do I escape a single quote in SQL Server?
  6. Simple way to transpose columns and rows in SQL?
  7. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  8. Only inserting a row if it’s not already there
  9. Unpivot with column name
  10. Selecting data from two different servers in SQL Server
  11. Most efficient way in SQL Server to get date from date+time?
  12. Generate SQL Create Scripts for existing tables with Query
  13. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  14. How do I delete from multiple tables using INNER JOIN in SQL server
  15. Check if a row exists, otherwise insert
  16. SQL : BETWEEN vs =
  17. How do I group on continuous ranges
  18. join comma delimited data column
  19. Need to list all triggers in SQL Server database with table name and table’s schema
  20. Count based on condition in SQL Server
  21. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  22. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  23. Alter user defined type in SQL Server
  24. Best equivalent for IsInteger in SQL Server
  25. Syntax of for-loop in SQL Server
  26. How to correctly insert newline in nvarchar [duplicate]
  27. How do you copy a record in a SQL table but swap out the unique id of the new row?
  28. Using IF ELSE statement based on Count to execute different Insert statements
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment