如何使SQL标量函数既具有确定性又具有变量缺省值

Question

--DROP FUNCTION fn_NonDeterministic;
--DROP FUNCTION fn_Deterministic

CREATE FUNCTION [dbo].[fn_NonDeterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @date AS DATETIME = '01/01/1900';
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_NonDeterministic'
GO

CREATE FUNCTION [dbo].[fn_Deterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @date AS DATETIME ;
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_Deterministic'

Answer 1

我不认为它喜欢从字符串到日期的转换，这是确定性的，因为它受到区域设置的影响。

/*
dROP FUNCTION fn_NonDeterministic;
DROP FUNCTION fn_Deterministic
*/


CREATE FUNCTION [dbo].[fn_NonDeterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    -- ********************************  this changes things ***********
    DECLARE @date AS datetime = datefromparts(1970,05,23);
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_NonDeterministic'
GO
CREATE FUNCTION [dbo].[fn_Deterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @date AS DATETIME ;
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_Deterministic'