Random record from a database table (T-SQL)

by

Is there a succinct way to retrieve a random record from a sql server table?

Yes

SELECT TOP 1 * FROM table ORDER BY NEWID()

Explanation

A NEWID() is generated for each row and the table is then sorted by it. The first record is returned (i.e. the record with the “lowest” GUID).

Notes

  1. GUIDs are generated as pseudo-random numbers since version four:

    The version 4 UUID is meant for generating UUIDs from truly-random or
    pseudo-random numbers.

    The algorithm is as follows:

    • Set the two most significant bits (bits 6 and 7) of the
      clock_seq_hi_and_reserved to zero and one, respectively.
    • Set the four most significant bits (bits 12 through 15) of the
      time_hi_and_version field to the 4-bit version number from
      Section 4.1.3.
    • Set all the other bits to randomly (or pseudo-randomly) chosen
      values.

    —A Universally Unique IDentifier (UUID) URN Namespace – RFC 4122

  2. The alternative SELECT TOP 1 * FROM table ORDER BY RAND() will not work as one would think. RAND() returns one single value per query, thus all rows will share the same value.

  3. While GUID values are pseudo-random, you will need a better PRNG for the more demanding applications.

  4. Typical performance is less than 10 seconds for around 1,000,000 rows — of course depending on the system. Note that it’s impossible to hit an index, thus performance will be relatively limited.

More Related Contents:

  1. Random Weighted Choice in T-SQL
  2. SQL Server – error when creating stored procedure
  3. How can foreign key constraints be temporarily disabled using T-SQL?
  4. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  5. How can I escape square brackets in a LIKE clause?
  6. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  7. Parameter Sniffing (or Spoofing) in SQL Server
  8. SQL Server String or binary data would be truncated
  9. Datatype for storing ip address in SQL Server
  10. How to change identity column values programmatically?
  11. SQL Server Regular expressions in T-SQL
  12. Is there a way to loop through a table variable in TSQL without using a cursor?
  13. Maximum size of a varchar(max) variable
  14. best way to convert and validate a date string
  15. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  16. Concatenating Column Values into a Comma-Separated List
  17. Is there a way to simplify a NULL compare of 2 values
  18. TSQL Try / Catch within Transaction or vice versa?
  19. What is the maximum number of characters that nvarchar(MAX) will hold?
  20. TSQL: Create a view that accesses multiple databases
  21. Sequence as default value for a column
  22. Aggregate bitwise-OR in a subquery
  23. How do I flush the PRINT buffer in TSQL?
  24. OR Operator Short-circuit in SQL Server
  25. How to change default language for SQL Server?
  26. T-SQL incrementing counter based on change in a column value
  27. T-SQL XOR Operator
  28. How to wait for 2 seconds?
  29. What are row, page and table locks? And when they are acquired?
  30. time format in SQL Server

Leave a Comment