“order by newid()” – how does it work?

by

I know what NewID() does, I’m just
trying to understand how it would help
in the random selection. Is it that
(1) the select statement will select
EVERYTHING from mytable, (2) for each
row selected, tack on a
uniqueidentifier generated by NewID(),
(3) sort the rows by this
uniqueidentifier and (4) pick off the
top 100 from the sorted list?

Yes. this is pretty much exactly correct (except it doesn’t necessarily need to sort all the rows). You can verify this by looking at the actual execution plan.

SELECT TOP 100 * 
FROM master..spt_values 
ORDER BY NEWID()

The compute scalar operator adds the NEWID() column on for each row (2506 in the table in my example query) then the rows in the table are sorted by this column with the top 100 selected.

SQL Server doesn’t actually need to sort the entire set from positions 100 down so it uses a TOP N sort operator which attempts to perform the entire sort operation in memory (for small values of N)

Plan

More Related Contents:

  1. newid() inside sql server function
  2. incorrect syntax near keyword where [closed]
  3. How do I create a unique constraint that also allows nulls?
  4. How to convert SQL Server’s timestamp column to datetime format
  5. Local and global temporary tables in SQL Server
  6. Multiple rows to one comma-separated value in Sql Server [duplicate]
  7. SQL Server add auto increment primary key to existing table
  8. SQL Server: UPDATE a table by using ORDER BY
  9. SQL Server: Difference between PARTITION BY and GROUP BY
  10. How do you truncate all tables in a database using TSQL?
  11. EF5: Cannot attach the file ‘{0}’ as database ‘{1}’
  12. SQL Server: How to select all days in a date range even if no data exists for some days
  13. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  14. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  15. Is it possible to set a timeout for a SQL query on Microsoft SQL Server?
  16. Assign result of dynamic sql to variable
  17. Stored Procedure and Permissions – Is EXECUTE enough?
  18. Best way to store time (hh:mm) in a database
  19. Using variable in SQL LIKE statement
  20. Stored procedure – return identity as output parameter or scalar
  21. How do I select last 5 rows in a table without sorting?
  22. Why does a parameterized query produces vastly slower query plan vs non-parameterized query
  23. displaying multiple records by using resultset
  24. SELECT INTO a table variable in T-SQL
  25. SQL Server: Filter output of sp_who2
  26. How to display two digits after decimal point in SQL Server
  27. How can I insert binary file data into a binary SQL field using a simple insert statement?
  28. In SQL Server, how can I find everywhere a column is referenced?
  29. Is there an “IF EXISTS” test for a linked server?
  30. SQL Server stored procedure return code oddity

Leave a Comment