我可以使 SQL Server FORMAT 具有确定性吗? [英] Can I make SQL Server FORMAT deterministic?

问题描述

我想制作一个返回 YYYYMM 整数形式的 UDF，以便我可以轻松地按月划分某些内容.我正在尝试将此函数分配给 PERSISTED 计算列的值.

I want to make a UDF which returns an integer form of YYYYMM so that I can easily partition some things on month. I am trying to assign this function to the value of a PERSISTED computed column.

我目前有以下内容，效果很好:

I currently have the following, which works fine:

CREATE FUNCTION dbo.GetYearMonth(@pDate DATETIME2)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @fYear VARCHAR(4) = RIGHT('0000' + CAST(YEAR(@pDate) AS VARCHAR),4)
    DECLARE @fMonth VARCHAR(2) = RIGHT('00' + CAST(MONTH(@pDate) AS VARCHAR),2)

    RETURN CAST(@fYear + @fMonth AS INT)
END

但我认为使用 FORMAT 更简洁.我试过这个:

But I think it's cleaner to use FORMAT instead. I tried this:

CREATE FUNCTION dbo.GetYearMonth(@pDate DATETIME2)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @fYear VARCHAR(4) = FORMAT(@pDate,'yyyy', 'en-us')
    DECLARE @fMonth VARCHAR(2) = FORMAT(@pDate,'MM', 'en-us')

    RETURN CAST(@fYear + @fMonth AS INT)
END

但是这个函数是不确定的.有没有办法使 FORMAT 具有确定性?或者有没有更好的方法来做到这一点，使 UDF 具有确定性?

But this function is nondeterministic. Is there a way to make FORMAT deterministic? Or is there a better way to do this, making the UDF deterministic?

推荐答案

有趣的问题，所以我做了一些挖掘，但没有得出任何结论:)

Interesting question so I did some digging and came up with nothing conclusive :)

从确定性和非确定性函数开始

Starting with Deterministic and Nondeterministic Functions

没有明确列出 FORMAT 但声明:

which doesn't explicitly list FORMAT but states:

所有聚合和字符串内置函数都是确定性的.

All of the aggregate and string built-in functions are deterministic.

和字符串函数的链接

再次声明，此页面声明

所有内置字符串函数都是确定性的.这意味着它们在任何时候使用一组特定的输入值调用时都会返回相同的值.

All built-in string functions are deterministic. This means they return the same value any time they are called with a specific set of input values.

但是， 上的页面FORMAT 对这个话题保持沉默.

However, the page on FORMAT is silent on the subject.

FORMAT 使用 并不排除它的确定性，但 doco 对 FORMAT 的实际实现保持沉默.

FORMAT uses the CLR which doesn't preclude it from being deterministic but the doco is silent as to the actual implementation of FORMAT.

最后，怀疑这是 doco(或代码)中的错误 a 快速搜索连接 什么都没透露.

Finally, suspecting this is a bug in the doco (or the code) a quick search of connect reveals nothing.

测试用例怎么样?(欢迎评论此代码的有效性……)

How about a test case? (Comments welcome on the validity of this code…)

CREATE FUNCTION WhatAmI(@Number INTEGER) 
RETURNS NVARCHAR
WITH SCHEMABINDING
AS 
BEGIN
RETURN FORMAT(@Number, 'd')
END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('WhatAmI'),'IsDeterministic')

-----------
0

(1 row(s) affected)

Nup，这是不确定的.

Nup, that's nondeterministic.

所以，这是一个有趣的练习，但没有定论.

So, an interesting exercise but nothing conclusive.

那么我们学到了什么(根据 BOL)?如果内置函数是不确定的，就没有办法做到这一点.有些取决于参数类型，有些应该是但不是.

So what did we learn (according to BOL)? If a built-in function is nondeterministic, there's no way of making it so. Some are both depending on the parameter types, and some are supposed to be but aren't.

这篇关于我可以使 SQL Server FORMAT 具有确定性吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！