T-SQL to create random number

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()