T-SQL to create random number

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.


FROM [dbo].[Numbers]




SQL Server Scripts


Recent Posts

Tag cloud