How do I return random numbers as a column in SQL Server 2005?

Question

I'm running a SQL query on SQL Server 2005, and in addition to 2 columns being queried from the database, I'd also like to return 1 column of random numbers along with them. I tried this:

select column1, column2, floor(rand() * 10000) as column3 
from table1

Which kinda works, but the problem is that this query returns the same random number on every row. It's a different number each time you run the query, but it doesn't vary from row to row. How can I do this and get a new random number for each row?

Have a look at my answer, the answer you marked correct is incorrect (you asked for numbers between 0 and 10000) and inefficient. — Sam Saffron, Jan 29 '09 at 22:14
So... you voted down the question because you don't like the answer I chose? — Joshua Carmody, Jan 30 '09 at 15:10
I've accepted your answer instead, because it is objectively better (although Adam's did what I needed as well). However, I want to emphasize that questions should not be voted down (or up) based on the answers attached to them. Please vote based on the merits of the question. — Joshua Carmody, Jan 30 '09 at 15:16
See: http://stackoverflow.com/questions/493739/etiquette-for-correcting-old-questions-with-incorrect-answers/495187#495187 — Sam Saffron, Jan 30 '09 at 21:02
I gave you a +1 because now that it is correct, note the correct answer is Timothys I get no points for it ... — Sam Saffron, Jan 30 '09 at 21:03
Also, I gave you a -1 because you had an answer that was marked correct that did not work fill your specifications and was very dangerous from a performance perspective ... — Sam Saffron, Jan 30 '09 at 21:04
The NEWID() function is the best way posted here to force the database to do it but I don't understand *why* you want this. If there's nothing meaningful about the number why not just generate it in whatever application that is consuming the output? Your big expensive database server has better work to do than this. — Transact Charlie, Apr 25 '13 at 22:10
This question/answer may be valuable: http://stackoverflow.com/a/9039661/47226 — Aaron Hoffman, Apr 29 '13 at 20:29

score 41 · Accepted Answer · edited Jan 29 '09 at 12:59

41

I realize this is an older post... but you don't need a view.

select column1, column2, 
  ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 10000 as column3 
from table1

edited Jan 29 '09 at 12:59

Sam Saffron

128,308
78
326
506

answered Jan 29 '09 at 12:55

Timothy Khouri

31,315
21
88
128

How about you take this answer, I'll delete mine and add the mod to yours – Sam Saffron Jan 29 '09 at 12:58
I also get to give this a +1 :) – Sam Saffron Jan 29 '09 at 13:00
This question/answer may also be valuable: shorter version of above: http://stackoverflow.com/a/9039661/47226 – Aaron Hoffman Apr 29 '13 at 20:32

score 4 · Answer 2 · edited May 23 '17 at 11:45

WARNING

Adam's answer involving the view is very inefficient and for very large sets can take out your database for quite a while, I would strongly recommend against using it on a regular basis or in situations where you need to populate large tables in production.

Instead you could use this answer.

Proof:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

go 

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END

go 

create table bigtable(i int)

go 

insert into bigtable 
select top 100000 1 from sysobjects  a
join sysobjects b on 1=1

go 

select cast(dbo.RandNumber() * 10000 as integer) as r into #t from bigtable 
-- CPU (1607) READS (204639) DURATION (1551)

go

select ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 10000 as r  into #t1 
from bigtable
-- Runs 15 times faster - CPU (78) READS (809) DURATION (99)

Profiler trace:

alt text http://img519.imageshack.us/img519/8425/destroydbxu9.png

This is proof that stuff is random enough for numbers between 0 to 9999

-- proof that stuff is random enough 
select avg(r) from #t
-- 5004
select STDEV(r) from #t
-- 2895.1999 

select avg(r) from #t1
-- 4992
select STDEV(r) from #t1
-- 2881.44 


select r,count(r) from #t
group by r 
-- 10000 rows returned 

select r,count(r) from #t1
group by r 
-- 10000 row returned

I will confirm that for numbers between 1 and 10000, hold tight — Sam Saffron, Jan 29 '09 at 23:24

score 3 · Answer 3 · answered Dec 07 '10 at 03:00

3

select RAND(CHECKSUM(NEWID()))

answered Dec 07 '10 at 03:00

denis_n

783
8
29

this actually works. you get my point. simple and answers the questions directly. thanks! – ekkis Jul 17 '13 at 06:56

score 3 · Answer 4 · answered Sep 18 '08 at 18:23

Adam's answer works really well, so I marked it as accepted. While I was waiting for an answer though, I also found this blog entry with a few other (slightly less random) methods. Kaboing's method was among them.

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

score 1 · Answer 5 · answered Aug 03 '09 at 14:45

1

According to my testing, the answer above doesn't generate a value of 10000 ever. This probably isn't much of a problem when you are generating a random between 1 and 10000, but the same algorithm between 1 and 5 would be noticable. Add 1 to your mod.

answered Aug 03 '09 at 14:45

The question wasn't about generating random numbers in a specific range. Any random numbers would do. 10000 is just an arbitrary number I used in my code example. – Joshua Carmody Aug 06 '09 at 13:19

score 1 · Answer 6 · edited Apr 25 '13 at 21:58

I use c# for dealing with random numbers. It's much cleaner. I have a function I use to return a list of random number and a unique key, then I just join the uniqueKey on the row number. Because I use c#, I can easily specify a range within which the random numbers must fall.

Here are the steps to making the function: http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html

Here is what my query ends up looking like:

SELECT 
   rowNumber, 
   name, 
   randomNumber
FROM dbo.tvfRandomNumberList(1,10,100) 
INNER JOIN (select ROW_NUMBER() over (order by int_id) as 'rowNumber', name from client        
            )as clients
ON clients.rowNumber = uniqueKey

score 1 · Answer 7 · edited Jan 06 '13 at 20:22

This snippet seems to provide a reasonable substitute for rand() in that it returns a float between 0.0 and 1.0. It uses only the last 3 bytes provided by newid() so total randomness may be slightly different than the conversion to VARBINARY then INT then modding from the recommended answer. Have not had a chance to test relative performance but seems fast enough (and random enough) for my purposes.

SELECT CAST(SubString(CONVERT(binary(16), newid()), 14, 3) AS INT) / 16777216.0 AS R

score 1 · Answer 8 · answered Sep 18 '08 at 17:54

You need to use a UDF

first:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

second:

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END

test:

SELECT dbo.RandNumber(), *
FROM <table>

Above borrowed from Jeff's SQL Server Blog

score 1 · Answer 9 · answered Sep 18 '08 at 17:58

1

For SQLServer, there are a couple of options.
1. A while loop to update an empty column with one random number at a time
2. A .net Assembly that contains a function that returns a random number

answered Sep 18 '08 at 17:58

hova

2,811
20
19

score 1 · Answer 10 · edited May 15 '15 at 11:30

1

Query

select column1, column2, cast(new_id() as varchar(10)) as column3 
from table1

edited May 15 '15 at 11:30

Ullas

11,450
4
33
50

answered Sep 18 '08 at 17:58

Jonas Lincoln

9,567
9
35
49

score 1 · Answer 11 · answered Sep 18 '08 at 18:42

You might like to consider generating a UUID instead of a random number using the newid function. These are guaranteed to be unique each time generated whereas there is a significant chance that some duplication will occur with a straightforward random number (and depending on what you're using it for could give you a phenominally hard to debug error at a later point)

score 1 · Answer 12 · answered Sep 18 '08 at 19:44

1

newid() i believe is very resource intensive. i recall trying that method on a table of a few million records and the performance wasn't nearly as good as rand().

answered Sep 18 '08 at 19:44

Adam

7,800
2
25
24

How do I return random numbers as a column in SQL Server 2005?

12 Answers12

Linked

Related