Início / dba / Perguntas / 174739
Accepted
SqlZim
Asked: 2017-05-28 05:03:25 +0800 CST

format() é uma função de string embutida não determinística... certo?

  • 772

Antes de postar um item de conexão sobre a falta de documentação sobre isso, alguém confirmará que não estou simplesmente perdendo algo aqui?

Na página de documentos, onde formatestá listado como uma função de string:

"Todas as funções de string internas são determinísticas." - Funções de string (Transact-SQL)

Também não há menção de formatser não determinista em páginas relacionadas:

No entanto, ao tentar criar uma coluna computada persistente:

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

Retorna o seguinte erro:

A coluna computada 'date_formatted_01' na tabela 't' não pode ser mantida porque a coluna não é determinística.

A documentação informa que

Se o argumento de cultura não for fornecido, o idioma da sessão atual será usado.

mas adicionar um argumento de cultura não muda as coisas

Isso também falha

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

demonstração do rextester: http://rextester.com/ZMS22966

demonstração do dbfiddle.uk: http://dbfiddle.uk/?rdbms=sqlserver_next&fiddle=7fc57d1916e901cb561b551af144aed6

sql-server

3 respostas

  1. A FORMATdocumentação agora foi atualizada (em resposta ao seu item Connect ) para dizer:

    A FORMATfunção é não determinística.

    Da mesma forma, as String Functions (Transact-SQL) agora incluem:

    Todas as funções de string internas, exceto FORMATsão determinísticas.

    • 7
  2. Best Answer

    Uma função não é necessariamente determinística ou não determinística. Existem algumas funções que podem ser determinísticas dependendo de como são usadas :

    As funções a seguir nem sempre são determinísticas, mas podem ser usadas em exibições indexadas ou índices em colunas computadas quando são especificadas de maneira determinística.

    CASTe CONVERTsão tais exemplos. Com base nos testes que você fez até agora, acho que é justo dizer que FORMATnem sempre é determinístico, apesar de ser uma função string. Se você quiser saber se às vezes é determinista, a única técnica que consigo pensar é tentar maneiras diferentes o suficiente de chamá-lo até que você esteja satisfeito. Por exemplo, vamos considerar FORMATcomo aplicado a números. Existem apenas dez tipos diferentes de entrada numérica :

    tipos de entrada numérica

    Também parece haver apenas nove formatos numéricos diferentes . É possível tentar criar colunas persistentes para todas as combinações possíveis. Alguns códigos para fazer isso estão abaixo:

    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;

    Aqui está uma amostra da saída:

    saída de código de teste

    Não consegui adicionar nenhuma das colunas à tabela para alguns valores de entrada e culturas. Não tentei exaustivamente todas as culturas possíveis porque não consigo encontrar uma lista delas no SQL Server.

    No mínimo, parece seguro concluir que a documentação referente ao determinismo de FORMATestá incorreta, então eu recomendaria enviar um item de conexão para ele.

    • 5

  3. Eu não sou um usuário regular do sqlserver, então posso estar enganado, mas meu palpite é que o formato não é uma função de string. De acordo com a documentação:

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

    format recebe um tipo de data ou um tipo numérico como argumento. Se tudo o que você quer fazer é pegar a parte do ano de uma data, você não pode usar a função do ano?

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

    se você quiser uma representação de string:

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

relate perguntas