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