1

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?

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
MilesToGoBeforeISleep
  • 183
  • 1
  • 2
  • 17
  • 1
    You need to give it a style parameter to make it deterministic. – Martin Smith Apr 26 '17 at 19:36
  • 1
    You need to add a `style` to the convert for it to be deterministic: `CONVERT(varchar(7),[date],103)` for instance – Lamak Apr 26 '17 at 19:36
  • 2
    Slight detour...not sure that a date datatype makes for a good primary key. And to be honest the YrMn column could easily be handled in the front end with formatting of the already existing data. – Sean Lange Apr 26 '17 at 19:37

1 Answers1

1

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

SqlZim
  • 37,248
  • 6
  • 41
  • 59