CODEWITHSUNDEEP
sql-server-2008selectrandom
Nilesh Pethani
Nilesh Pethani

Reputation: 822

how to pick randome number from the number list

I need to choose one number randomly from the list of bigint in sql server 2008. the number list is not continue

For example : The number list may be like this

1578 1579 1580 1587 1589 1590 1595 1603

Thanks in Adv.

Upvotes: 5

Views: 3091

Answers (3)

Sepster
Sepster

Reputation: 4849

Other answers do not scale well to large tables.

This one does, and is available from SQL2005 onwards.

SELECT TOP 1 * FROM <table_name> TABLESAMPLE (10 PERCENT)

From "further down" in Selecting Rows Randomly from a Large Table:

Often, when questions about how to select random rows are asked in discussion groups, the NEWID query is proposed; it is simple and works very well for small tables. However, the NEWID query has a big drawback when you use it for large tables. The ORDER BY clause causes all of the rows in the table to be copied into the tempdb database, where they are sorted. This causes two problems:

The sorting operation usually has a high cost associated with it. Sorting can use a lot of disk I/O and can run for a long time. In the worst-case scenario, tempdb can run out of space. In the best-case scenario, tempdb can take up a large amount of disk space that never will be reclaimed without a manual shrink command. What you need is a way to select rows randomly that will not use tempdb and will not get much slower as the table gets larger.

Upvotes: 0

Fung
Fung

Reputation: 3558

Selecting Rows Randomly from a Large Table

SELECT TOP 1 BigIntColumn FROM Table1
ORDER BY NEWID()

Upvotes: 2

Kirk B.
Kirk B.

Reputation: 456

I've had success with ordering the top 1 result of a select by newid():

select top 1 WhateverId from YourTable order by newid();

Upvotes: 4

Related Questions