how does SELECT TOP works when no order by is specified?

by

There is no guarantee which two rows you get. It will just be the first two retrieved from the table scan.

The TOP iterator in the execution plan will stop requesting rows once two have been returned.

Likely for a scan of a heap this will be the first two rows in allocation order but this is not guaranteed. For example SQL Server might use the advanced scanning feature which means that your scan will read pages recently read from another concurrent scan.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. When no ‘Order by’ is specified, what order does a query choose for your record set?
  3. What is the difference between using a cross join and putting a comma between the two tables?
  4. How to update and order by using ms sql
  5. How to select only the first rows for each unique value of a column?
  6. How do I perform an IF…THEN in an SQL SELECT?
  7. Inserting multiple rows in a single SQL query? [duplicate]
  8. Passing a varchar full of comma delimited values to a SQL Server IN function
  9. SQL update query using joins
  10. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  11. Is there a combination of “LIKE” and “IN” in SQL?
  12. SQL query to select dates between two dates
  13. In SQL, how can you “group by” in ranges?
  14. is there an advantage to varchar(500) over varchar(8000)?
  15. T-SQL: Deleting all duplicate rows but keeping one [duplicate]
  16. Selecting COUNT(*) with DISTINCT
  17. SQL: IF clause within WHERE clause
  18. Where value in column containing comma delimited values
  19. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  20. Pivot using SQL Server 2000
  21. Equivalent of Oracle’s RowID in SQL Server
  22. Sequence vs identity
  23. How to store image in SQL Server database tables column [duplicate]
  24. SQL “between” not inclusive
  25. Can I set ignore_dup_key on for a primary key?
  26. SQL Server replace, remove all after certain character
  27. How to find column names for all tables in all databases in SQL Server
  28. Inserting rows into a table with one IDENTITY column only [duplicate]
  29. SQL Row_Number() function in Where Clause
  30. When using GETDATE() in many places, is it better to use a variable?

Leave a Comment