Wednesday, November 30, 2011

generate random number for each row in sql

Sometimes, you might come across the situation when you have to generate random values for each row in a resultset . 

In this article, I am sharing how you can achieve it. 

Idea here is to generate another column in "SELECT" statement that gives you random values for each row each time the query is executed.
Table and Data Script
CREATE TABLE TAB (
    ID VARCHAR(10), NAME VARCHAR(10)
)
GO
INSERT INTO TAB
SELECT 1, 'Anil' UNION ALL 
SELECT 2, 'Sunil' UNION ALL 
SELECT 3, 'Amit' UNION ALL 
SELECT 4, 'Sumit' UNION ALL 
SELECT 5, 'Rajesh'

In SQL Server, there is in-built function RAND() to generate random values, but when it is used in SELECT statement, it generates a static value for each row, so this is not going to help us in this scenario. However, NEWID() generates a random value for each row. Let's execute a script to see the difference

SELECT  RAND() AS RAND, NEWID() AS NEWID, ID, NAME
FROM    TAB
OUTPUT

Check the result, you will find RAND() function is giving static value for each row but NEWID() is giving random values, so NEWID() is going to help us in getting random row numbers. Now, Lets see how we can use NEWID() to generate the random() values

SELECT  ABS(CHECKSUM(NEWID())%100) AS NEWID, ID, NAME
FROM    TAB
OUTPUT

Check the above result, here is a new column NEWID in which there is random values for each rows. Try running the script multiple times, you will find that this will generate different values each time for each row

0 comments:

Post a Comment