July 23, 2005 at 4:06 pm
I've got a table structure thus:
create
table "Report" (
"ReportID"
int identity not null,
"ReportTypeCode"
varchar(5) not null,
"ReportStatusCode"
varchar(3) null,
"lYearCode"
varchar(4) null,
"ReportDesc"
varchar(50) null,
"ReportNotes"
varchar(500) null,
"NotesPrintFlag"
bit null,
"ReportDate"
smalldatetime not null,
"DateOpenFrom"
smalldatetime null,
"DateOpenTo"
smalldatetime null,
"ReportCompleteFlag"
bit null,
"ReportTreeKey"
As (ReportTypeCode + Convert(varchar(20), ReportDate)) Persisted,
"ChangeStamp"
timestamp null)
but I get an error from SQL 2005 when I try and create this table of:
Msg 4931, Level 16, State 1, Line 17
Computed column 'ReportTreeKey' in table 'Report' cannot be persisted because the column is non-deterministic.
Could someone explain why this computed column is non-deterministic? BTW I have a default on the ReportDate column of today's date.
What is the correct way to create a computed, persisted column composed of the 2 columns above?
Regards
July 23, 2005 at 11:33 pm
I think this could work :
Convert(varchar(20), ReportDate, 112))
The 112 is the iso format which is deterministic. That conversion isn't deterministic because the date format of the connexion makes the formating change and needs to be calculated on the fly. See BOLs for more details.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply