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 
Begin 
 Print 'Invalid range, Starting number shoul be less than End Number' 
 Return 
End 
SET NOCOUNT ON 
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 
 Begin 
  Insert Into #tblTemp (RandomNumber) Values (null) 
  Set @CurrentRec=@CurrentRec+1 
 End 

Set @CurrentRec=1 
While @CurrentRec<=@MyCount 
 Begin 
  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 
End 

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

    Login

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 
    Begin  
      Set @RecNum = CAST((rand()* @MyCount) AS int) + @Start_Num 
      Insert Into #tblTemp (RandomNumber) Values (@RecNum )     
      Set @CurrentRec=@CurrentRec+1 
    End

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 
   BEGIN 
    PRINT 'Invalid range, Starting number shoul be less than End Number' 
    RETURN 
   END 
  SET NOCOUNT Off

  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 
   BEGIN
    SET @RecNum = CAST((RAND() * @MyCount) AS int) + @Start_Num 
    INSERT  INTO #tblTemp (RandomNumber) VALUES (@RecNum)      
    SET @CurrentRec = @CurrentRec + 1 
  END
  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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    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.

    Back to top