Generate Random Number for each row in SQL Server

In this post, I am sharing how  to generate random values for each row in a result set in SQL Server

Idea here is to generate another column in SELECT statement that gives you random values for each row each time the query is executed.

Let's create a Table and populate some data in it.

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