sql server—为什么此sql函数tickstodatetime不确定？如何使它具有确定性？

anauzrmj 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(453)

我有以下函数“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。。
所以我的问题是为什么这个函数是“非确定性”的，我该如何使它具有确定性？从我在互联网上读到的，它只是说添加“与schemabinding”，但正如你所看到的，我已经添加了，它仍然不起作用。
我做错什么了？

sql sql-server sql-function Function calculated-columns

来源：https://stackoverflow.com/questions/62913676/why-is-this-sql-function-tickstodatetime-non-deterministic-and-how-to-make-it-d

2条答案

按热度按时间

xggvc2p61#

使用 CAST 到/从旧的日期和时间数据类型是不确定的。使用 CONVERT 从旧日期和时间到/从旧日期和时间的数据类型可以是确定性的，例如，如果使用的样式代码是确定性的或不是来自字符串的。您正在使用 CAST 这里，所以不是。
从确定性和非确定性函数：
以下函数并不总是确定性的，但当它们以确定性方式指定时，可以在索引视图或计算列的索引中使用。

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);

赞(0）回复(0）举报 2021-07-26

bybem2ql2#

正如@larnu极好答案的延伸：
我最终得到的功能是：

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 = Convert(datetime,'17530101',112)
        else
            if (@t=3155378975999999999)
                set @result = Convert(datetime,'99991201',112)
            else
                set @result = Convert(datetime,(@t - 599266080000000000) / 10000000 / 24 / 60 / 60)
    return @result
END

GO

并通过

select object_id('TicksToDateTime')
select OBJECTPROPERTYEX(11863109, 'IsDeterministic')

赞(0）回复(0）举报 2021-07-26

我来回答

sql server—为什么此sql函数tickstodatetime不确定？如何使它具有确定性？

2条答案

相关问题

热门标签

最新问答