Problem solve Get help with specific problems with your technologies, process and projects.

Random numbers with T-SQL

The follwong T-SQL query batch will show you how to generate random numbers.

The following T-SQL query batch will show you how to generate random numbers in SQL Server. If the start number is greater than the end number, it will give a custom message and exit from the query batch.

Declare  @Start_Num int,@End_Num int 
set @Start_Num=1 
set @End_Num=10 

If @Start_Num>@End_Num 
 Print 'Invalid range, Starting number shoul be less than End Number' 
Declare @RecNum int, @CurrentRec int, @MyCount int 
Set @MyCount=@End_Num-@Start_Num+1 

Create Table #tblTemp (CurrentRecNo int identity(1,1), RandomNumber int null) 

Set @CurrentRec=1 
While @CurrentRec<=@MyCount 
  Insert Into #tblTemp (RandomNumber) Values (null) 
  Set @CurrentRec=@CurrentRec+1 

Set @CurrentRec=1 
While @CurrentRec<=@MyCount 
  Set @RecNum=rand()*@MyCount+@Start_Num 
  If @Start_Num<0 Set @RecNum=@RecNum-1 

         Update #tblTemp Set RandomNumber=@RecNum 
                Where CurrentRecNo=@CurrentRec 
                And @RecNum Not In (Select RandomNumber From #tblTemp 
                                    Where RandomNumber Is Not Null) 

  If @@rowcount>0 Set @CurrentRec=@CurrentRec+1 

Select RandomNumber From #tblTemp 
Drop Table #tblTemp 


Change  @Start_Num and @End_Num for different ranges. 

Reader Feedback

Jorge C. writes: Here's what I think:

1) If the value of @Start_Num (=1 ) and @End_Num (=10 ) are established in the script's body, the validation of their values is not necesary. This would be necesary if we pass these two values as parameters; and what happens if @Start_Num = @End_Num in the validation?

2) The statement on line 12 (Create Table) must be preceded by the statement (Drop Table) in line 30, that's drop and create the table when we need it. If we need to use the same table after running the batch (as happens in line 29), it's not better have it enabled--the inconvenience is that we need to check first if the table exists.

-- if exist table then
-- begin
Drop Table #tblTemp 
-- end
Create Table #tblTemp (CurrentRecNo int identity(1,1), RandomNumber int null) 

3) On line 12, why do we want to create the field CurrentRecNo as identity? A well-known set of unique random numbers (just RandomNumber) or a list of pair values CurrentRecNo and RandomNumber:

Create Table #tblTemp (CurrentRecNo int identity(1,1), RandomNumber int null) 

4) The while loop on lines 14 to 18 is not necesary--this will be replaced with the one on lines 19 to 28.

5) On line 16 the ramdom value must be Integer and between @Start_Num and @End_Num the while loop on lines 19 to 28, this will be simplified like this:

11> Set @MyCount = @End_Num - @Start_Num + 1 
19> Set @CurrentRec=1 
    While @CurrentRec <= @MyCount 
      Set @RecNum = CAST((rand()* @MyCount) AS int) + @Start_Num 
      Insert Into #tblTemp (RandomNumber) Values (@RecNum )     
      Set @CurrentRec=@CurrentRec+1 

Thus, the script will be:

  DECLARE  @Start_Num int , @End_Num int 
  SET @Start_Num=1 
  SET @End_Num=10 
  IF @Start_Num >= @End_Num 
    PRINT 'Invalid range, Starting number shoul be less than End Number' 

  DROP TABLE #tblTemp 
  CREATE TABLE #tblTemp (CurrentRecNo int IDENTITY(1,1), RandomNumber int null) 

  DECLARE @RecNum int, @CurrentRec int, @MyCount int 

  SET @MyCount = ( @End_Num - @Start_Num ) + 1  
  SET @CurrentRec = 1 

  WHILE @CurrentRec <= @MyCount 
    SET @RecNum = CAST((RAND() * @MyCount) AS int) + @Start_Num 
    INSERT  INTO #tblTemp (RandomNumber) VALUES (@RecNum)      
    SET @CurrentRec = @CurrentRec + 1 
  SELECT RandomNumber FROM #tblTemp 

Create and drop (or drop and create), is not really a very good way to ensure that the table is empty, right? Why not just test if the table exists? If it's not, then create it. If it exists, then delete it. And what happens if another user is working on it?

I'm a novice, but these are my impressions. Am I wrong?

For More Information

  • What do you think about this tip? E-mail the Editor at with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.