10

Before I post a connect item regarding the lack of documentation about this, will someone confirm that I am not simply missing something here?

On the docs page where format is listed as a string function:

"All built-in string functions are deterministic." - String Functions (Transact-SQL)

There is also no mention of format being nondeterministic on related pages:


However, when attempting to create a persisted computed column:

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

Returns the following error:

Computed column 'date_formatted_01' in table 't' cannot be persisted because the column is non-deterministic.

The documentation states that

If the culture argument is not provided, the language of the current session is used.

but adding a culture argument doesn't change things

This also fails

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

rextester demo: http://rextester.com/ZMS22966

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_next&fiddle=7fc57d1916e901cb561b551af144aed6

  • 1
    This also fails: alter table #t add date_formatted_01 as CONVERT(VARCHAR(20), FORMAT(date_col, 'YYYY', 'en-US')) persisted;. Not sure why FORMAT isn't deterministic, especially when specifying the culture. The date_formatted column can be VARCHAR(20) (still persisted) and set via Trigger using FORMAT. Or SQLCLR works. Using the SQL# SQLCLR library (that I wrote), you can do ALTER TABLE SQL#.t ADD date_formatted_03 AS SQL#.Date_Format(date_col, 'd', 'en-US') PERSISTED; (table is owned by SQL# since table and function owner need to be the same). – Solomon Rutzky May 27 '17 at 15:12
5

A function isn't necessarily deterministic or nondeterministic. There are some functions which can be deterministic depending on how they are used:

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.

CAST and CONVERT are such examples. Based on the testing that you've done so far, I think that it's fair to say that FORMAT is not always deterministic, despite being a string function. If you want to know if it's sometimes deterministic the only technique I can think of is to try enough different ways to call it until you are satisfied. For example, let's consider FORMAT as applied to numbers. There are only ten different numeric input types:

numeric input types

There also appear to be only nine different numeric formats. It's possible to try to create persisted columns for all possible combinations. Some code to do that is below:

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;

Here's a sample of the output:

test code output

I wasn't able to get any of the columns to be added to the table for a few input values and cultures. I did not exhaustively try all possible cultures because I can't find a list of them within SQL Server.

At minimum it seems safe to conclude that the documentation regarding the determinism of FORMAT is incorrect, so I would recommend submitting a connect item for it.

6

The FORMAT documentation has now been updated (in response to your Connect item) to say:

The FORMAT function is nondeterministic.

Likewise, String Functions (Transact-SQL) now includes:

All built-in string functions except FORMAT are deterministic.

1

I'm not a regular user of sqlserver, so I might be mistaken, but my guess is that format is not a string function. According to the documentation:

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

format takes either a date type or a numeric type as argument. If all you want to do is to grab the year-part of a date, cant you use the year function?

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

if you want a string representation:

alter table t 
    add date_formatted_01 as cast(year(date_col) as char(4)) persisted;
  • 1
    It's listed as a string function in the docs. i.stack.imgur.com/aj0T2.png – Martin Smith May 27 '17 at 14:13
  • 1
    @MartinSmith, interesting. Personally I find it counter intuitive and it also becomes logically inconsistent with "All built-in string functions are deterministic." – Lennart May 27 '17 at 14:30
  • @Lennart I appreciate the alternative to the example, but the example wasn't important. – SqlZim May 27 '17 at 14:50
  • 1
    @SqlZim, I figured that your example was just an example but I added an alternative just in case. I'm not sure what your question is exactly, is it whether format is a string function or not, whether it's deterministic or not, or whether all of that is badly documented? – Lennart May 27 '17 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.