Random numbers with T-SQL

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

    Requires Free Membership to View

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 tdichiara@techtarget.com 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.

This was first published in January 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.