by Amol
17. February 2011 19:03
We often need of creating random numbers which are useful to create sample or test data.
Here are two ways to create the random number in SQL Server
1. Create random number less the given number
Below query will create a random number between 0 and 1000
SELECT ABS(CAST(NEWID() AS binary(6)) % 1000) AS Value
2. Create random number between minimum and maximum numbers
Below query will create the random number between minimum and maximum numbers
DECLARE @MinValue integer = 10,
@MaxValue integer = 100
SELECT CAST((@MaxValue - @MinValue + 1) * RAND() AS integer) + @MinValue
3. Each time randomly select top N records from a table
Below query will select top 10 records from table Numbers. In this scenario I have populated Numbers table with 1 to 10000 numbers. Each time you fire a query, we will be able to get different top N numbers from Numbers table.
SELECT TOP 10 *
FROM [dbo].[Numbers]
ORDER BY NEWID()