函数调用了多少次?

Lets have a following query:

让我们进行以下查询:

SELECT * FROM {tablename} WHERE ColumnId = dbo.GetId()

where dbo.GetId() is non-deterministic user defined function. The question is whether dbo.GetId() is called only once for entire query and its result is then applied or is it called for each row? I think it is called for every row, but I don't know of any way how to prove it.

其中，dbo.GetId()是不确定的用户定义函数。问题是，dbo.GetId()是否只对整个查询调用一次，然后应用它的结果，还是每一行都调用它?我认为每一行都需要它，但是我不知道怎么证明它。

Also would following query be more efficient?

下面的查询是否更有效?

DECLARE @Id int
SET @Id = dbo.GetId()
SELECT * FROM {tablename} WHERE ColumnId = @Id

3 个解决方案

#1

I doubt this is guaranteed anywhere. Use a variable if you want to ensure it.

我怀疑这在任何地方都能得到保证。如果您想要确保变量，请使用它。

I amended @Prdp's example

我修改@Prdp的例子

CREATE VIEW vw_rand
AS
  SELECT Rand() ran

GO

/*Return 0 or 1 with 50% probability*/
CREATE FUNCTION dbo.Udf_non_deterministic ()
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT CAST(10000 * ran AS INT) % 2
         FROM   vw_rand)
  END

go

SELECT *
FROM master..spt_values
WHERE dbo.Udf_non_deterministic() = 1

In this case it is only evaluated once. Either all rows are returned or zero.

在这种情况下，只计算一次。要么返回所有行，要么返回0。

The reason for this is that the plan has a filter with a startup predicate.

原因是该计划有一个带有启动谓词的过滤器。

The startup expression predicate is [tempdb].[dbo].[Udf_non_deterministic]()=(1).

启动表达式谓词是[tempdb].[dbo].[Udf_non_deterministic]()=(1)。

This is only evaluated once when the filter is opened to see whether to get rows from the subtree at all - not for each row passing through it.

当打开过滤器以查看是否从子树获取行时，只对它进行一次计算，而不是对通过子树的每一行进行计算。

But conversely the below returns a different number of rows each time indicating that it is evaluated per row. The comparison to the column prevents it being evaluated up front in the filter as with the previous example.

但是反过来，下面的代码每次返回不同数量的行，表明它是每一行计算的。与该列的比较可以防止它在过滤器前面与前面的示例一样对其进行评估。

SELECT *
FROM master..spt_values
WHERE dbo.Udf_non_deterministic() = (number - number)

And this rewrite goes back to evaluating once (for me) but CROSS APPLY still gave multiple evaluations.

这个重写回到了评估一次(对我来说)但是交叉应用仍然给出了多重评估。

SELECT *
FROM master..spt_values
OUTER APPLY(SELECT  dbo.Udf_non_deterministic() ) AS C(X)
WHERE X = (number - number)

#2

Here is one way to prove it

有一种方法可以证明这一点

View

View is created to add a Nondeterministic inbuilt Functions inside user defined function

创建视图是为了在用户定义的函数中添加不确定性的内置函数

CREATE VIEW vw_rand
AS
  SELECT Rand() ran

Nondeterministic Functions

Now create a Nondeterministic user defined Functions using the above view

现在使用上面的视图创建一个不确定的用户定义函数

CREATE FUNCTION Udf_non_deterministic ()
RETURNS FLOAT
AS
  BEGIN
      RETURN
        (SELECT ran
         FROM   vw_rand)
  END

Sample table

样表

CREATE TABLE #test
  (
     id   INT,
     name VARCHAR(50)
  )

INSERT #test
VALUES (1,'a'),
       (2,'b'),
       (3,'c'),
       (4,'d')

SELECT dbo.Udf_non_deterministic (), *
FROM   #test 

Result:

结果:

id  name    non_deterministic_val
1   a       0.203123494465542
2   b       0.888439497446073
3   c       0.633749721616085
4   d       0.104620204364744

As you can see for all the rows the function is called

正如您所看到的，函数被调用的所有行

#3

Yes it does get called once per row. See following thread for debugging functions SQL Functions - Logging

是的，它确实会被每一行调用一次。请参见下面的线程调试SQL函数—日志记录

And yes the below query is efficient as the function is called only once.

是的，下面的查询是有效的，因为函数只调用一次。

DECLARE @Id int
SET @Id = dbo.GetId()
SELECT * FROM {tablename} WHERE ColumnId = @Id