To Generate Random Numbers (Int) between Rang:
-- Create the variables for the random number generation
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int

-- This will create a random number between 1 and 999
SET @Lower = 1 -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
SELECT @Random


To Generate Random Float Numbers:
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

Hi,

Generate Random Numbers

SQL Server has a built-in function that generates a random number, the RAND() mathematical function.The RAND math function returns a random float value from 0 through 1.

SELECT RAND() AS [RandomNumber]

RandomNumber
---------------------
0.34344339282376501


The output of the RAND function will always be a value between 0 and 1.
If you want to generate a random integer number, all you have to do is multiply it by the maximum value you want generated and then get rid of the decimal places. One way of getting rid of the decimal places is by CASTing it to INT. Here's an example of generating a random number with a maximum value of 999,999:


SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]


RandomNumber
------------
163819



SELECT TOP 10 RAND() AS [RandomNumber], [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers]

RandomNumber CustomerID
-------------------- ----------
0.21090395019612362 AL001
0.21090395019612362 AN001
0.21090395019612362 AN003
0.21090395019612362 AR005
0.21090395019612362 BE067
0.21090395019612362 AUS07
0.21090395019612362 BL076
0.21090395019612362 BO405
0.21090395019612362 BA909
0.21090395019612362 BM809


"Note: Repetitive invocations of RAND() in a single query will produce the same value."

The NEWID() Way

he different way of generating a random number without using the RAND mathematical function. The NEWID system function can be used to generate a random numeric value as can be seen from the following SELECT statement.

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

RandomNumber
------------
403663676

The NEWID system function returns a unique value of uniqueidentifier data type. order to convert this to an integer data type, it first has to be converted to VARBINARY then it can be converted to integer, as can be seen by the two CAST statements. The resulting integer value can be positive and negative.

SELECT TOP 10 ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],
[CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers]


RandomNumber CustomerID
-------------------- ----------
1120919216 AL001
1227765350 AN001
677704826 AN003
151612960 AR005
755868329 BE067
1566550720 AUS07
1511953511 BL076
1280157238 BO405
1283976604 BA909
989554291 BM809


-R.Gopi