gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 19:21:59 24 4
gpt4 key购买 nike

我有以下函数“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”,但正如你所看到的,我已经添加了它,但它仍然不起作用。

我做错了什么?

最佳答案

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

关于sql - 为什么这个 SQL 函数 TicksToDateTime 是不确定的?以及如何使其具有确定性?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/62913676/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com