Nondeterministic functions are functions that are not guaranteed to return the same output when invoked multiple times with the same input. When you invoke nondeterministic built-in functions in a query (such as RAND and GETDATE), those functions are invoked once for the whole query and not once per row. The only exception to this rule is the NEWID function, which generates a globally unique identifier (GUID). NEWID is the only nondeterministic built-in function that will be invoked once per row.

To demonstrate this behavior of nondeterministic functions, run the following code, which queries the Orders table in the Northwind database; invokes the functions RAND, GETDATE, and NEWID; and generates the output shown in abbreviated form in Table 6-11:

USE Northwind;

SELECT RAND() AS rnd, GETDATE() AS dt, NEWID() AS guid, OrderID AS oid
FROM dbo.Orders;

You can observe that both RAND and GETDATE were invoked only once for the whole query, and their result values were copied to all rows. On the other hand, NEWID was invoked once per row, generating a different value in each row.

Suppose that you had the need to invoke the RAND function for each row. You might have thought of invoking RAND from a UDF and then invoking the UDF in an outer query, knowing that a UDF is invoked once per row. Here’s an attempt to create such a UDF called fn_rand:

IF OBJECT_ID('dbo.fn_rand') IS NOT NULL
  DROP FUNCTION dbo.fn_rand;
GO
CREATE FUNCTION dbo.fn_rand() RETURNS FLOAT
AS
BEGIN
  RETURN RAND();
END
GO

However, this attempt fails and produces the following error:

Msg 443, Level 16, State 1, Procedure fn_rand, Line 6
Invalid use of side-effecting or time-dependent operator in 'rand' within a function.

The error tells you that your function is not allowed to have side effects, and the RAND function does change an internal state.

There’s a back door that allows you to implicitly invoke RAND from a UDF. Create a view that invokes RAND, and query the view from the UDF, like so:

IF OBJECT_ID('dbo.fn_rand') IS NOT NULL
  DROP FUNCTION dbo.fn_rand;
GO
IF OBJECT_ID('dbo.VRand') IS NOT NULL
  DROP VIEW dbo.VRand;
GO
CREATE VIEW dbo.VRand AS SELECT RAND() AS r;
GO
CREATE FUNCTION dbo.fn_rand() RETURNS FLOAT
AS
BEGIN
  RETURN (SELECT r FROM dbo.VRand);
END
GO

You can test the fn_rand UDF by invoking it in a query against the Orders table, which will generate the output shown in abbreviated form in Table 6-12:

SELECT dbo.fn_rand() AS rnd, OrderID AS oid FROM dbo.Orders;

SQL Server 2000 disallowed the invocation of nondeterministic functions from UDFs. SQL Server 2005 is more lenient in the sense that it inspects more properties of a UDF and distinguishes between functions that have side effects and functions that don’t. For example, in SQL Server 2000 you couldn’t invoke the GETDATE function from a UDF, but in SQL Server 2005 you can:

IF OBJECT_ID('dbo.fn_getdate') IS NOT NULL
  DROP FUNCTION dbo.fn_getdate;
GO
CREATE FUNCTION dbo.fn_getdate() RETURNS DATETIME
AS
BEGIN
  RETURN GETDATE();
END
GO

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.fn_rand') IS NOT NULL
  DROP FUNCTION dbo.fn_rand;
GO
IF OBJECT_ID('dbo.VRand') IS NOT NULL
  DROP VIEW dbo.VRand;
GO
IF OBJECT_ID('dbo.fn_getdate') IS NOT NULL
  DROP FUNCTION dbo.fn_getdate;
GO