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

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

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close