Creating Nondeterministic Functions in SQL Server Using Rand()

Creating Nondeterministic functions in SQL Server using RAND()

Because it has side effects.

Constructs with side effects are not allowed in a function. The side effect that it has is to change some internal state that keeps track of the last rand() value issued.

I think you can get around it by including it in a View definition then selecting from the View.

using RAND() in a function

You could use a SQL CLR function if you really needed it in a function. Or if you only need it in one place, just put it in the stored procedure.

Personally I would opt towards not doing this sort of work inside SQL Server at all, if it could be helped. This is application logic, not database logic. Although without knowing the back story, I don't know what other constraints you may have.

Use RAND() in User Defined Function

The problem is that you cannot call a non-deterministic function from inside a user-defined function.

I got around this limitation by creating a view, call that function inside the view and use that view inside your function, something like this......

View Definition

CREATE VIEW vw_getRANDValue
AS
SELECT RAND() AS Value

Function Definition

ALTER FUNCTION getNumber(@_id int )
RETURNS DECIMAL(18,4)
AS
BEGIN
   DECLARE @RtnValue DECIMAL(18,4);
   SELECT TOP 1 @RtnValue = EmployeeID 
   FROM dbo.Employees
   ORDER BY EmployeeID DESC

   SET @RtnValue = (SELECT Value FROM vw_getRANDValue) * @RtnValue * (1.0000/100.0000) --<-- to make sure its not converted to int
    RETURN @RtnValue;
END

Create generate random number function in SQL Server

You can. However, it will require a little bit of extra legwork.

First, you need to create a view, like the one below:

create view dbo.sys_NDF
as
select rand() as [ValueRand], newid() as [ValueGUID],
  rand(checksum(newid())) as [SeededRand];
go

The trick is that you cannot call these system functions directly from your UDF, however you can query a view that returns their values. You can later expand it with other functions / columns if need be.

As such, your function starts to look like the following:

Create function [GetRandomNumber]()
RETURNS bigint as begin
return (select round(v.SeededRand * 10001 + 50000, 0) from dbo.sys_NDF v);
end;
go

What does it mean by Non-deterministic User-Defined functions can be used in a deterministic manner?

the BOL actually states:

The following functions are not
always deterministic, but can be
used in indexed views or indexes on
computed columns when they are
specified in a deterministic manner.

and then below it states what conditions must be met to make them deterministic.

E.g.

CAST - Deterministic unless used with
datetime, smalldatetime, or
sql_variant

In other words you need to meet those condition to use them in deterministic manner

For example when you create a table

CREATE TABLE [dbo].[deterministicTest](
    [intDate] [int] NULL,
    [dateDateTime] [datetime] NULL,
    [castIntToDateTime]  AS (CONVERT([datetime],[intDate],0)),
    [castDateTimeToInt]  AS (CONVERT([int],[dateDateTime],0)),
    [castIntToVarchar]  AS (CONVERT([varchar],[intDate],0))
) ON [PRIMARY]

you can apply index on castIntToVarchar but if you try to add index to castDateTimeToInt or castIntToDateTime you will get the following error:

Column 'castDateTimeToInt'(castIntToDateTime) in table 'dbo.deterministicTest' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

So the dateTime cannot be used neither as a source nor the target format of the CONVERT function if you want to stay deterministic

Function that creates unique reference no

The easy way to do that is to use the rand() built-in function to generate the random string (the middle part of your reference number). Unfortunately, SQL Server doesn't allow you to create a non-deterministic function which basically means you can't call rand() from within your own function.

An easy (but slightly ugly) workaround is to pass the random string in as a parameter because you are allowed to call rand() from a stored procedure. Sounds confusing? This is what it should look like (assuming your random string should only contain uppercase letters):

create function UniqueRefNum (@r1 float, @r2 float, @r3 float, @r4 float)
returns char(14)
begin
    -- Not sure if rand() might return 1.0
    -- If it does, the conversion code below would produce a character that's not an
    -- uppercase letter so let's avoid it just in case
    if @r1 = 1.0 set @r1 = 0
    if @r2 = 1.0 set @r2 = 0
    if @r3 = 1.0 set @r3 = 0
    if @r4 = 1.0 set @r4 = 0

    declare @now datetime
    set @now = getdate() -- or getutcdate()

    declare @m char(2)
    if month(@now) < 10
        set @m = '0' + month(@now)
    else
        set @m = month(@now)

    declare @d char(2)
    if day(@now) < 10
        set @d = '0' + day(@now)
    else
        set @d = day(@now)

    return @m + @d + '-' + 
           char(65 + cast(@r1 * 26 as int)) + 
           char(65 + cast(@r2 * 26 as int)) + 
           char(65 + cast(@r3 * 26 as int)) + 
           char(65 + cast(@r4 * 26 as int)) + 
           '-' + cast(year(@now) as varchar)
end

You then call the function from your stored procedure like this:

declare @uniqueRef char(14)
set @uniqueRef = dbo.UniqueRefNum(rand(), rand(), rand(), rand())

Is this a good or bad way of generating random numbers for each record?

I would not do this for a piece of software I wanted to continue working on future versions of SQL Server. I found a way to return a different values from RAND() for each row in a select statement. This discovery was 1) a bit of a hack and 2) was made on SQL Server 2005. It no longer works on SQL Server 2008. That experience makes me extra leary of relying on trickery to get rand() to return a random value per row.

Also, I believe SQL Server is allowed to optimize away the multiple calls to a UDF ... though that might be changing since they do allow some non-deterministic functions now.

For SQL Server 2005 only, a way to force rand() to execute per row in a select statement. Does not work on SQL Server 2008. Not tested on any version prior to 2005:

create table #t (i int)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)

select i, case when i = 1 then rand() else rand() end as r
from #t

1   0.84923391682467
2   0.0482397143838935
3   0.939738172108974

Also, I know you said you were not asking about the randomness of rand(), but I will a good reference is: http://msdn.microsoft.com/en-us/library/aa175776(SQL.80).aspx. It compares rand() to newid() and rand(FunctionOf(PK, current datetime)).

---

Related Topics