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".
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 @Qty AS INT DECLARE @price AS decimal DECLARE @pname AS varchar(150) WHILE(@first <= 1000) BEGIN SELECT @Qty=CAST(RAND() * 1000 AS INT) select @price=ROUND(RAND(CHECKSUM(NEWID())) * (100), 2) select @pname=CONVERT(varchar(150),SUBSTRING(CONVERT(varchar(150), NEWID()), 0, 7)) INSERT INTO [dbo].[tbProduct] (QtyAvail,UnitPrice,Pname) VALUES (@Qty,@price,@pname) SET @first += 1 END
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))