为什么此SQL函数TicksToDateTime是不确定的?以及如何使其具有确定性? [英] Why is this SQL Function TicksToDateTime non-deterministic? and how to make it deterministic?

问题描述

我具有以下功能"TicksToDateTime"

I have the following function "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

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

and the use it in a computed column of a table:

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

我现在正尝试为"CallDate"索引像这样的列:

I am now trying to index the "CallDate" column like so:

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

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

But the index fails because the column is "non-deterministic" and I can confirm that the function is also non-deterministic with:

select object_id('tickstodatetime')

select OBJECTPROPERTYEX(2127346643, 'IsDeterministic')

哪个返回假..

所以我的问题是，为什么这个函数不确定"?以及如何使它具有确定性?从我在互联网上阅读的内容来看，它只是说要添加"With SchemaBinding"，但是如您所见，我已经添加了它，但仍然无法正常工作.

So my question is why is this function "non-deterministic" and how do I make it deterministic? from what I read on the internet it just said add "With SchemaBinding" but as you can see I've added that and it still doesnt work.

我在做什么错了?

推荐答案

在旧的日期和时间数据类型中使用 CAST 不确定.对旧日期和时间数据类型使用 CONVERT 是确定性的，例如，如果使用的是确定性样式代码，或不是从字符串确定性的样式代码.您在这里使用 CAST ，所以不是.

Using CAST to/from the old date and time data types is not deterministic. Using CONVERT to/from the old date and time data types can be deterministic, for example if using a style code that is deterministic or not from a character string. You are using CAST here, so it isn't.

来自

以下功能并非总是确定性的，但是当以确定性方式指定它们时，可以在索引视图或计算列的索引中使用.

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 和样式代码.因此，例如，代替:

In this case, you need to use CONVERT and a style code. So for example, instead of:

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

您需要这样做:

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

这篇关于为什么此SQL函数TicksToDateTime是不确定的?以及如何使其具有确定性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！