sql - 为什么这个 SQL 函数 TicksToDateTime 是不确定的？以及如何使其具有确定性？

Question

我有以下函数“TicksToDateTime”

CREATE FUNCTION [dbo].[TicksToDateTime] (@t bigint)
RETURNS datetime
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstickstopic.asp
BEGIN 
    declare @result datetime
    if (@t=0)
        set @result = null
    else
        if (@t < 552877919999983334)
            set @result = cast ('1753-1-1' as datetime)
        else
            if (@t=3155378975999999999)
                set @result = cast ('9999-12-1' as datetime)
            else
                set @result = CAST((@t - 599266080000000000) / 10000000 / 24 / 60 / 60 AS datetime)
    return @result
END
GO

并在表的计算列中使用它:

[CallDateRaw] BIGINT NOT NULL,
[CallDate] AS ([dbo].[TicksToDateTime]([CallDateRaw])),

我现在尝试像这样索引“CallDate”列:

Create Index ExternalCalls_CallDate2 ON [External.Call] (CallDate)
GO

但是索引失败，因为该列是“不确定的”，我可以通过以下方式确认该函数也是不确定的:

select object_id('tickstodatetime')

select OBJECTPROPERTYEX(2127346643, 'IsDeterministic')

返回 false..

所以我的问题是为什么这个函数是“非确定性的”以及如何使其具有确定性？从我在互联网上读到的内容来看，它只是说添加“With SchemaBinding”，但正如你所看到的，我已经添加了它，但它仍然不起作用。

我做错了什么？

Answer 1

使用 CAST 与旧的日期和时间数据类型进行转换不是确定性的。使用 CONVERT 与旧日期和时间数据类型之间的转换可以是确定性的，例如，如果使用确定性或非来自字符串的样式代码。您在这里使用 CAST，所以它不是。

来自Deterministic and Nondeterministic Functions :

The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function                   Comments
all aggregate functions    All aggregate functions are deterministic unless they are specified with the    OVER and ORDER BY clauses. For a list of these functions, see Aggregate Functions (Transact-SQL).

CAST                       Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT                    Deterministic unless one of these conditions exists:
                           Source type is sql_variant.
                           Target type is sql_variant and its source type is nondeterministic.
                           Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. 
                           Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

在这种情况下，您需要使用CONVERT和样式代码。例如，而不是:

set @result = cast ('1753-1-1' as datetime)

您需要执行以下操作:

SET @Result = CONVERT(datetime, '17530101',112);