这是无法保证的。
您需要检查执行计划才能找到答案。一些例子。
CREATE FUNCTION dbo.FUNC1(@p1 int)
RETURNS int
AS
BEGIN
RETURN @p1 + 1
END
GO
CREATE FUNCTION dbo.FUNC2(@p1 int)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN @p1 + 1
END
GO
SELECT
OBJECTPROPERTYEX(OBJECT_ID('dbo.FUNC1'), 'IsDeterministic'),
OBJECTPROPERTYEX(OBJECT_ID('dbo.FUNC2'), 'IsDeterministic')
GO
FUNC2
被建造WITH SCHEMABINDING
并被视为确定性的。FUNC1
isn't.
SELECT
dbo.FUNC1(number) AS FUNC1,
dbo.FUNC2(number) AS FUNC2
FROM master..spt_values
WHERE dbo.FUNC1(number) >= 5 AND dbo.FUNC2(number) >= 5
ORDER BY dbo.FUNC1(number), dbo.FUNC2(number)
给出计划
|--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
|--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
|--Filter(WHERE:([test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])>=(5) AND [Expr1004]>=(5)))
|--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])))
|--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))
FUNC1
被评估两次(一次在过滤器中,一次在计算标量中,输出用于投影和排序的计算列),FUNC2
仅评估一次。
重写为
SELECT
FUNC1,
FUNC2
FROM master..spt_values
CROSS APPLY (SELECT dbo.FUNC1(number), dbo.FUNC2(number)) C(FUNC1, FUNC2)
WHERE FUNC1 >= 5 AND FUNC2 >= 5
ORDER BY FUNC1, FUNC2
稍微改变计划,两者都只评估一次
|--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
|--Filter(WHERE:([Expr1003]>=(5)))
|--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
|--Filter(WHERE:([Expr1004]>=(5)))
|--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])))
|--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))
现在对查询稍作修改
SELECT
FUNC1 + 10,
FUNC2 + 10
FROM master..spt_values
CROSS APPLY (SELECT dbo.FUNC1(number), dbo.FUNC2(number)) C(FUNC1, FUNC2)
WHERE FUNC1 >= 5 AND FUNC2 >= 5
ORDER BY FUNC1, FUNC2
给出与原始结果相反的结果FUNC2
被评估两次但是FUNC1
只有一次。
|--Compute Scalar(DEFINE:([Expr1005]=[Expr1003]+(10)))
|--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
|--Filter(WHERE:([Expr1003]>=(5)))
|--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
|--Filter(WHERE:([Expr1004]>=(5)))
|--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number]), [Expr1006]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])+(10)))
|--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))