sql-servernon-deterministicsql-convert

Is CONVERT non-deterministic?

I have the following table:

CREATE TABLE Portfolio.DailyStats
(
Date date NOT NULL PRIMARY KEY,
NAV int NOT NULL,
SP500 decimal(8,4) NULL,
R2K decimal(8,4) NULL,
NetExp decimal(8,4) NULL,
GrossExp decimal(8,4) NULL,
[YrMn]  AS (CONVERT([varchar](7),[Date])),
)
GO

I was thinking that I may need to build an indexed view on this at some point so I wanted to make sure my calculated column [YrMn] was deterministic. I ran this check:

Select COLUMNPROPERTY (OBJECT_ID('Portfolio.DailyStats'),'YrMn', 'IsDeterministic')

This returned a value of 0 indicating that the column is non-deterministic. I am struggling to figure out why this is the case given that the column value will always the same for a specific date. Does it have to do with the convert function and what is it?

Solution

  • It becomes deterministic if you include the third parameter for convert(), the style. e.g.

    [YrMn]  AS (convert([varchar](7),[Date],120))

    rextester demo: http://rextester.com/DFNA25042