Random Number in SQL Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Random Number in SQL Server 2000

Author

Topic

hnomani
Starting Member

35 Posts

Posted - 2002-11-21 : 16:04:46

I wanted to run an update on a column with more than 1 million rows and populate it with random numbers, what is the best way to do this in SQL Server 2000.

Thanks
Haseeb

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-21 : 16:15:45

What kind of numbers? (Any kind?)

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-21 : 16:21:14

search BOL for RAND

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-11-21 : 16:30:38

This is a MS trick for random records:

use Pubs
go
select top 5 * from dbo.authors
ORDER BY NEWID()

It doesn't give you a random number (unless you have an integer column and then the random number returned would just be a random value from the integer column), but it's a pretty cool trick.

hnomani
Starting Member

35 Posts

Posted - 2002-11-21 : 17:08:37

Thanks, that looks very promising.

quote:

This is a MS trick for random records:

use Pubs
go
select top 5 * from dbo.authors
ORDER BY NEWID()

It doesn't give you a random number (unless you have an integer column and then the random number returned would just be a random value from the integer column), but it's a pretty cool trick.

ankurgupta26
Starting Member

32 Posts

Posted - 2002-11-22 : 08:39:50

I tried that query. I found it strange. Every 11th time the query is executed it returns different data, other wise it returns the same data.
i.e
1st execution - same date
2nd execution - same date
3rd execution - same date
4th
5th
...
11th - different data

1st execution - same date
...
11th - different data

do i make sense ?? I am using sql 2000

Thanks !
AnkuR.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-22 : 21:53:59

Are you running it on a NT 4 server?

newid isn't guaranteed to be random. Only unique :)

Or are you talking about using something like this:


SELECT RAND(), .....
FROM ......

RAND with no arguments takes the current time as its seed value (I think anyway, close enough to the truth anyway) and it always returns the same value for any given seed. So, if you use it in a query, it'll probablly return the same value for each row.

I saw the following syntax in another post, but I don't remember who's it was. I've gotta find my bookmark to it again someday.


RAND(CAST(CAST(newid() AS binary(4)) AS int))

This takes a generated guid and cuts it down to 4 bytes which is enough for an integer. It then uses the integer as a seed for rand. I've found the results a little skewed, but if you don't require statistical randomness then.......

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 11/22/2002 22:03:08

ankurgupta26
Starting Member

32 Posts

Posted - 2002-11-27 : 05:28:45

I tried this query :
select top 5 * from dbo.authors
ORDER BY NEWID()

i have sql 2000 on win nt. and, i am connecting to the sever using win 98 machine.

it's like for around 7-8 times the results are same, then it changes just for once.....and again the same data as per first execution is returned.

when i run the query - select distinct newid() from dbo.authors. i always get 23 rows (same as in authors table) which means newid() is returining new id during each run.

Thanks !
AnkuR.

Tim
Starting Member

392 Posts

Posted - 2002-11-27 : 16:51:36

there is s script in the scipt forum for random stuff.

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources