October 31, 2005 at 9:48 am
Is there a random number generator at all in SQL Server? Can it be used in a stored procedure? Or will i have to do random number with the program and pass them to SQL Server?
October 31, 2005 at 9:58 am
This will give you what you need (think I got it from here originaly) delete or add a line to get more or less digits:
select CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
+CAST ( CONVERT ( INT, 10*rand()) AS CHAR(1))
Don't realy know how well it will perform resource wise though.
October 31, 2005 at 9:59 am
Research Using RAND and NEWID in BOL.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 31, 2005 at 11:09 am
thanks guys!
November 1, 2005 at 4:14 am
I have always used newid() for a random selection of records something like:
select top 100 name from names
order by newid()
Paul.
November 1, 2005 at 4:17 am
Paul's approach can be used highly effectively if you have a table full of integers. Just use Top 1 and the filter criteria, and you can use it whereever you need a random number.
NewID() is very handy when trying to generate test data I find.
November 2, 2005 at 12:51 pm
A couple of thoughts.
Depending on your application, these may or may not be suitable. For example they would not be good for a high security app.
Another thing to remember is that many random number generators do not repeat (or repeat only after the whole cycle is completed) whereas a real world random number can repeat. (NewID by definition will not repeat). If you are using it to test an application where repetitions of data are possible, make sure repeats do occasionally occur (truncating a random number will often produce occasional repeats).
Finally notice that rand(seed) has aother odd behavior, unlike normal random number generators, similar seeds start at similar 'random' values.
...
-- FORTRAN manual for Xerox Computers --
November 24, 2005 at 12:23 pm
Regarding the rand(seed) option, something that gives you better randomization is to run the results through a reverse(rand(seed)) operation. rand(1), rand(2), rand(3) sorted asc will return results in the same order as 1,2,3. By running through the reverse function you get a much more random response.
Also, don't forget that ordering by any function will not allow the use of an index so ordering on a resultset with N records, although appearing relatively fast for a powerful machine, will not scale.
August 9, 2007 at 8:36 am
Hi I am a noob to sql and am creating a random number generator on sql 2005. I got some code from a old proc but dont understand one portion.
SET @Lower = 1000 -- The lowest random number
SET @Upper = 9999 -- The highest random number
SELECT @random = Round(((@Upper - @Lower - 1) * Rand() + @Lower), 0)
I need a 4 digit number generated but what is the -1 for?
Thanks,
August 9, 2007 at 2:20 pm
And, Peter's fine method works not only for single variables, but for a whole table, as well.
SELECT (ABS(CHECKSUM(NEWID())) % 10000) AS RandomNumber
FROM Master.dbo.Syscolumns
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply