主页 / dba / 问题 / 174739
Accepted
SqlZim
Asked: 2017-05-28 05:03:25 +0800 CST

format() 是一个不确定的内置字符串函数......对吧？

  • 772

在我发布关于缺少相关文档的连接项目之前，有人会确认我不仅仅是在这里遗漏了什么吗？

在format被列为字符串函数的文档页面上：

“所有内置的字符串函数都是确定性的。” -字符串函数 (Transact-SQL)

在相关页面上也没有提到format不确定性：

但是，在尝试创建持久计算列时：

create table t (date_col date); 
insert into t values (getdate());
alter table t add date_formatted_01 as format(date_col,'YYYY') persisted;

返回以下错误：

无法保留表“t”中的计算列“date_formatted_01”，因为该列是不确定的。

该文档指出

如果未提供 culture 参数，则使用当前会话的语言。

但添加文化论点并没有改变事情

这也失败了

alter table t add date_formatted_02 as format(date_col, 'd', 'en-US' ) persisted

rextester 演示：http ://rextester.com/ZMS22966

dbfiddle.uk 演示：http ://dbfiddle.uk/?rdbms=sqlserver_next&fiddle=7fc57d1916e901cb561b551af144aed6

sql-server

3 个回答

  1. 该FORMAT文档现已更新（响应您的Connect 项目）说：

    该FORMAT函数是不确定的。

    同样，字符串函数 (Transact-SQL)现在包括：

    FORMAT除了确定性的所有内置字符串函数。

    • 7
  2. Best Answer

    函数不一定是确定性的或非确定性的。有些函数可以确定，具体取决于它们的使用方式：

    以下函数并不总是确定性的，但可以在以确定性方式指定时用于索引视图或计算列的索引。

    CAST就是这样CONVERT的例子。根据您到目前为止所做的测试，我认为可以公平地说它FORMAT并不总是确定性的，尽管它是一个字符串函数。如果您想知道它有时是否是确定性的，我能想到的唯一技术就是尝试足够多的不同方式来调用它，直到您满意为止。例如，让我们考虑FORMAT应用于数字。只有十种不同的数字输入类型：

    数字输入类型

    似乎也只有九种不同的数字格式。可以尝试为所有可能的组合创建持久化列。下面是一些代码：

    DECLARE @FormatValue INT = 76767; -- change this if you want
DECLARE @FormatCulture VARCHAR(10) = 'en-US'; -- change this if you want
DECLARE @Format VARCHAR(1);
DECLARE @FormatType VARCHAR(10);
DECLARE @SQLForColumn VARCHAR(200);
DECLARE @TestNumber INT = 0;

BEGIN

    DROP TABLE IF EXISTS dbo.TargetTable;
    CREATE TABLE dbo.TargetTable (ID INT);

    DROP TABLE IF EXISTS #ColumnAddResults;
    CREATE TABLE #ColumnAddResults (
    FormatType VARCHAR(10),
    [Format] VARCHAR(1), 
    Succeeded VARCHAR(1), 
    ErrorMessage VARCHAR(1000)
    );

    drop table if exists #Types;
    create table #Types (FormatType VARCHAR(10));

    INSERT INTO #Types VALUES
    ('bigint'), ('int'), ('smallint'), ('tinyint'), ('decimal')
    , ('numeric'), ('float'), ('real'), ('smallmoney'), ('money');

    drop table if exists #Formats;
    create table #Formats ([Format] VARCHAR(1));

    INSERT INTO #Formats VALUES 
    ('C'), ('D'), ('E'), ('F'), ('G'), ('N'), ('P'), ('R'), ('X');

    DECLARE format_statements CURSOR LOCAL FAST_FORWARD FOR 
    SELECT #Types.FormatType, #Formats.[Format]
    FROM #Formats
    CROSS JOIN #Types;

    OPEN format_statements;

    FETCH NEXT FROM format_statements   
    INTO @FormatType, @Format;  

    WHILE @@FETCH_STATUS = 0  
    BEGIN
        SET @TestNumber = @TestNumber + 1;
        SET @SQLForColumn = 'alter table dbo.TargetTable add NewColumn' + CAST(@TestNumber AS VARCHAR(10))
        + ' as FORMAT(CAST(' +  CAST(@FormatValue AS VARCHAR(10)) + ' AS ' + @FormatType + '), '
        + '''' + @Format + ''', ''' + @FormatCulture + ''') persisted';

        BEGIN TRY
            EXEC (@SQLForColumn);
            INSERT INTO #ColumnAddResults VALUES (@FormatType, @Format, 'Y', NULL);
        END TRY
        BEGIN CATCH
            INSERT INTO #ColumnAddResults VALUES (@FormatType, @Format, 'N', ERROR_MESSAGE());
        END CATCH;

        PRINT @SQLForColumn;

        FETCH NEXT FROM format_statements   
        INTO @FormatType, @Format;  
    END;

    CLOSE format_statements;  
    DEALLOCATE format_statements;  

    SELECT * FROM dbo.TargetTable;
    SELECT * FROM #ColumnAddResults;
    DROP TABLE #ColumnAddResults;

END;

    这是输出示例：

    测试代码输出

    对于一些输入值和区域性，我无法将任何列添加到表中。我没有详尽尝试所有可能的区域性，因为我无法在 SQL Server 中找到它们的列表。

    至少可以肯定地得出结论，关于 的确定性的文档FORMAT是不正确的，因此我建议为它提交一个连接项。

    • 5

  3. 我不是 sqlserver 的普通用户，所以我可能弄错了，但我的猜测是 format 不是字符串函数。根据文档：

    https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

    format 采用日期类型或数字类型作为参数。如果你只想获取日期的年份部分，你不能使用年份函数吗？

    alter table t 
    add date_formatted_01 as year(date_col) persisted;

    如果你想要一个字符串表示：

    alter table t 
    add date_formatted_01 as cast(year(date_col) as char(4)) persisted;
    • 1

相关问题