How to Return Random Numbers as a Column in SQL Server 2005

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

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

How do I generate a random number for each row in a T-SQL select?

Take a look at SQL Server - Set based random numbers which has a very detailed explanation.

To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution:

ABS(CHECKSUM(NewId())) % 14

To change your range, just change the number at the end of the expression. Be extra careful if you need a range that includes both positive and negative numbers. If you do it wrong, it's possible to double-count the number 0.

A small warning for the math nuts in the room: there is a very slight bias in this code. CHECKSUM() results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14.

As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. When CHECKSUM() results in 14-19, these correspond to results 0-5. Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. It's easier to demonstrate this visually. Below is the entire possible set of results for our imaginary integer range:


Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

You can see here that there are more chances to produce some numbers than others: bias. Thankfully, the actual range of the Int type is much larger... so much so that in most cases the bias is nearly undetectable. However, it is something to be aware of if you ever find yourself doing this for serious security code.

How can I fill a column with random numbers in SQL? I get the same value in every row

Instead of rand(), use newid(), which is recalculated for each row in the result. The usual way is to use the modulo of the checksum. Note that checksum(newid()) can produce -2,147,483,648 and cause integer overflow on abs(), so we need to use modulo on the checksum return value before converting it to absolute value.

UPDATE CattleProds
SET    SheepTherapy = abs(checksum(NewId()) % 10000)
WHERE  SheepTherapy IS NULL

This generates a random number between 0 and 9999.

Random numbers from database

SELECT TOP 500
    CONVERT(INT, CONVERT(VARBINARY(16), NEWID()))
FROM
    dbo.MyTable

How to request a random row in SQL?

See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link):

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

How to generate random number that doesn't exist in other table?

If I proper understood you then try to get random number by using RAND() function(if you use SQL Server 2005 or higher). For example:

DECLARE @Random INT
DECLARE @Upper INT = 1
DECLARE @Lower INT = 999
DECLARE @RandomField  VARCHAR(10)

SET @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SET @RandomField  = 'AUTOCT' + CAST(@Random as varchar(5))

IF NOT EXISTS (select 1 from TBL_2 where RANDOM2 = @RandomField)
BEGIN
  INSERT INTO TBL_1 (RANDOM1) VALUES (@RandomField)
END

update random numbers for top 100 rows in sql?

In SQL 2008

update top (100) MyTable
set MyField = cast(cast(crypt_gen_random(1) as int) * 100.0 / 256 as int)

I believe the same will work in SQL 2005.

[Edit]

If it doesn't work in SQL 2005, you can do this:

update top (100) MyTable
set MyField = abs(cast(newid() as binary(6)) % 100)

SQL Server: how to insert random integers into table?

you can use select CAST(RAND() * 1000000 AS INT) AS [RandomNumber] for generating or selecting random integers .
so the full query will be something like that :

    DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum

regards..