Results 1 to 4 of 4

Thread: Random Select

  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Random Select

    How Can I select a Random set of records from a database table?

  2. #2
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Ok Got it from http://www.petefreitag.com/item/466.cfm

    SELECT TOP 1 column FROM table
    ORDER BY NEWID()

    Should I be careful with this use of the NEWID Function?

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    You should at least know that this method reads every page in the table meaning that if you have a large table then this will consume a lot of memory and it will place a shared lock on the entire table or on every key in the index (depending of number of rows in the table).

    Instead you could use th built in TABLESAMPLE clause and do a TOP 1.
    It is not as random as the method you found and it sometimes it doesn't event return any rows due to the way TABLESAMPLE works internally (read about it in BOL), but it does only read one page which results in much better concurrency.

    select top 1 * from MyTable tablesample (x percent or rows)

    The closer x is to the total number of rows in your table the lower is the randomness but the chances tha rows are returned increases. The closer x is to 0 the higher randomness but the chances that no rows are returned increases. You should test it yourself and find a suitable value for x.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by kaffenils
    You should ...
    k- just to clarify...TABLESAMPLE is available only with SQL 2005. Doesn't work with SQL 2000 or earlier.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •