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.
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
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
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
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 TABOUTPUT
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 TABOUTPUT
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
No comments:
Write Comments