T-sql randomization function

It is impressive how frequently Microsoft updates its sQL server database product. The current version is SQL server 2012. It seems just like yesterday I was posting on SQL Server 2008!  In any case, I continue to run into many needs to create random numbers in particular integers.

How to do that? To create a function that returns from 0 to the max possible integer value using T-SQL (for Microsoft SQL Server) try this. open a new query window and copy and paste the following:

-- note per msdn the max integer value is 2,147,483,647
declare @min int,@max int
set @min = 0
set @max = 2147483647

select convert(varchar(100),floor(@max*rand()+@min))

The result should be number between the min and max values. Note how the convert function makes the output a varchar to make it possible to print in the sql window.

There is also method which is better for some applications where better randomization is required. The parameter is the byte size and because we are interested in integers, which is 4 bytes, we use a parameter value of 4.

select convert(integer,crypt_gen_random(4))

 

If you need a random number function to persist you need to create one for example a sql server function. However when I tried to do this I got error:

Msg 443, Level 16, State 1, Procedure getRandomInteger, Line 16
Invalid use of a side-effecting operator 'rand' within a function.

Here you have to use a view to use the function to avoid the error.

CREATE VIEW [RandomNumber_v]
AS
SELECT        FLOOR(2147483647 * RAND() + 0) AS randNumber

GO

 

Then you can create the function.

CREATE FUNCTION getRandomInteger
(

)
RETURNS integer
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ReturnVal int

	select @ReturnVal=RandNumber from RandomNumber_v

	-- Return the result of the function
	RETURN @ReturnVal

END

Then you can call it from any sql statement like this, where you can specify a schema/user:

select [username].[getRandomInteger]() as randomNumber

You can view a page that uses sql server to generate random text using the same basic idea. The page uses randomization internal to SQL Server to generate randomly chosen text to be used for key generation purposes for example (link here).