Join Class

SQL random function: sql generate randon number

In this post we learn about random function in sql server, ms-sql provides built-in random function like RAND() RAND, how to generate number in sql server.

select RAND(2) -- 0.713610626184182
select RAND() -- 0.217821139260039

As you can see in above rand() function in sql can get you the random number, but in real-time application development we need various different form of random number, random character string, random decimal number etc.

If you want to generate random integer number in sql without having any decimal in front of the number, here is the query.

SELECT CAST(RAND() * 1000 AS INT) --794

Above query will give you three-digit integer number randomly, in case you want more than three digit number, then increase the zero in above query , like instead of 1000, make it 100000.

Now suppose we want random decimal number in sql query, we can use random function like example below, where we can specify how many number we want after decimal.

select ROUND(RAND(CHECKSUM(NEWID())) * (100), 2) -- 74.14

if want to generate three number after decimal, you can write 3 in above query instead of 2 (100), 2.

now, let's try to generate some random alphanumeric string in sql query using same random function.

select CONVERT(varchar(150),SUBSTRING(CONVERT(varchar(150), NEWID()), 0, 7))

Above query will generate a alphanumeric string value like "58BB53".

Insert random records in sql table [for testing]

Sometimes, we need to check query with large number of data, for example we want to insert one thousands records in our sql table, and we need to generate some random test data to insert into table, in such situation following use of random function in sql query will help.

Adding some random product with price and quantity into product table.

DECLARE @first AS INT = 1
DECLARE @price AS decimal
DECLARE @pname AS varchar(150)
WHILE(@first <= 1000)
select @price=ROUND(RAND(CHECKSUM(NEWID())) * (100), 2)
select @pname=CONVERT(varchar(150),SUBSTRING(CONVERT(varchar(150), NEWID()), 0, 7))
    INSERT INTO [dbo].[tbProduct]
    SET @first += 1	

In above example you can see how different type of data can be generated in sql using same random function, 1. CAST(RAND() * 1000 AS INT), 2. =ROUND(RAND(CHECKSUM(NEWID())) * (100), 2), 3. CONVERT(varchar(150), SUBSTRING(CONVERT(varchar(150), NEWID()), 0, 7))

Hire SQL Developer
SQL random function
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
Generate random number in sql server
MS SQL Examples | Join MS SQL Course