6.4  逐行调用UDF

不确定性函数是这样一种函数，当用相同的输入多次调用它时，它不保证返回相同的输出。当你在查询中调用内置的不确定性函数时（比如RAND和GETDATE），这些函数在整个查询中只被调用一次，而不是每行都调用。唯一的例外是NEWID函数，它生成一个全球唯一的标识符（GUID）。NEWID是唯一的每行都调用的内置不确定性函数。

为演示不确定性函数的这种行为，运行下面的代码，它将查询Northwind数据库中的Orders表，并调用RAND、GETDATE 和 NEWID，生成的输出以简化形式显示在表6-11中。

USE Northwind;

SELECT RAND() AS rnd, GETDATE() AS dt, NEWID() AS guid, OrderID AS oid

FROM dbo.Orders;

表6-11  调用不确定性函数的查询输出 (被简化)

rnd	dt	guid	oid
0.23575580157313	2005-12-19 14:18:00.157		10249
0.23575580157313	2005-12-19 14:18:00.157		10251
0.23575580157313	2005-12-19 14:18:00.157		10258
0.23575580157313	2005-12-19 14:18:00.157		10260
0.23575580157313	2005-12-19 14:18:00.157		10265
0.23575580157313	2005-12-19 14:18:00.157		10267
0.23575580157313	2005-12-19 14:18:00.157		10269
0.23575580157313	2005-12-19 14:18:00.157		10270
0.23575580157313	2005-12-19 14:18:00.157		10274
0.23575580157313	2005-12-19 14:18:00.157		10275
…	…	…	…

你会看到RAND 和 GETDATE在整个查询中只被调用了一次，它们的结果被复制到所有行。而NEWID每行都被调用，每行都生成不同的值。

如果为每一行调用RAND函数。你可能已经想到从UDF中调用RAND并在外部查询中调用UDF，因为UDF是每行都调用的。下面的代码尝试创建名为fn_rand的UDF。

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

然而，尝试将失败并产生下面的错误。

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.

这个错误表示函数不允许产生副作用，RAND函数可以改变内部状态。

不过有一个后门可以让你在UDF中显式地调用RAND。创建一个视图，该视图调用RAND，然后在UDF中查询该视图，就像这样：

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

你可以在查询Orders时调用fn_rand UDF以测试该函数，它产生的输出以简化形式显示在表6-12中。

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

表6-12  调用fn_rand UDF 的查询的输出(被简化)

rnd	oid
0.126413837261193	10248
0.222567782284458	10249
0.475723707976473	10250
0.57880518253848	10251
0.169390263927576	10252
0.337301740768919	10253
0.489646055111808	10254
0.826464402198423	10255
0.232679419042244	10256
0.152765690787598	10257
…	…

SQL Server 2000不允许在UDF中调用不确定函数。SQL Server 2005检查UDF的属性以区分它是否会产生副作用，从这一点来看，SQL Server 2005更宽容些。例如，在SQL Server 2000中你不能在UDF中调用GETDATE函数，但SQL Server 2005中就可以：

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