I'm trying to create a persisted computed column to make a distinct clause maybe a little faster
here is my code
ALTER TABLE xxx ADD groupset AS (
master_id+ company_code+FileLocation_FileBeginsWith
+ISNULL(despatch_group,'')
+CONVERT(varchar(100),ISNULL(despatch_group_start_date,'01/01/1900'))
+CONVERT(varchar(100),ISNULL(despatch_group_end_date,'01/01/1900'))
)PERSISTED
but i'm getting an error that the column cannot be created as one or more of the columns is not deterministic
all of these are just stored fields (no getdates or anything) and nne of the values ever get changed
is it the way I've ordered the convert and isnull on the date fields?
MVDBA
January 2, 2020 at 3:52 pm
You need to specify an explicit format (conversion code) for the date conversions, viz:
CONVERT(varchar(100),ISNULL(despatch_group_start_date,'01/01/1900'), <format_code_needed_here>)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 2, 2020 at 5:55 pm
I would recommend using CONCAT instead of the plus sign...it will simplify the code:
CONCAT(master_id, company_code, FileLocation_FileBeginsWith, despatch_group
, convert(char(8), isnull(despatch_group_start_date, '19000101'), 112)
, convert(char(8), isnull(despatch_group_end_date, '19000101'), 112))
If you don't want a date value to be included - you can change it to:
CONCAT(master_id, company_code, FileLocation_FileBeginsWith, despatch_group
, convert(char(8), despatch_group_start_date, 112), convert(char(8), despatch_group_end_date, 112))
CONCAT will take care of the NULL values and return an empty string.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2020 at 8:48 am
ok - I took both suggestions on board
ALTER TABLE tblPOS_Digital_Output__RunFor7Days___DataProduced ADD groupset AS (
CONCAT(master_id, company_code, FileLocation_FileBeginsWith, despatch_group
, convert(char(8), isnull(despatch_group_start_date, '19000101'), 112)
, convert(char(8), isnull(despatch_group_end_date, '19000101'), 112))
)PERSISTED
but I still get
Msg 4936, Level 16, State 1, Line 12
Computed column 'groupset' in table 'tblPOS_Digital_Output__RunFor7Days___DataProduced' cannot be persisted because the column is non-deterministic.
MVDBA
it may be because of the implicit date conversion.
try the following - but change the convert(date to be the same datatype as the corresponding start/end_date field
ALTER TABLE tblPOS_Digital_Output__RunFor7Days___DataProduced ADD groupset AS (
CONCAT(master_id, company_code, FileLocation_FileBeginsWith, despatch_group
, convert(char(8), isnull(despatch_group_start_date, convert(date, '19000101', 112)), 112)
, convert(char(8), isnull(despatch_group_end_date, convert(date, '19000101', 112)), 112))
)PERSISTED
January 3, 2020 at 12:23 pm
it may be because of the implicit date conversion.
try the following - but change the convert(date to be the same datatype as the corresponding start/end_date field
ALTER TABLE tblPOS_Digital_Output__RunFor7Days___DataProduced ADD groupset AS (
CONCAT(master_id, company_code, FileLocation_FileBeginsWith, despatch_group
, convert(char(8), isnull(despatch_group_start_date, convert(date, '19000101', 112)), 112)
, convert(char(8), isnull(despatch_group_end_date, convert(date, '19000101', 112)), 112))
)PERSISTED
works like a dream - still no idea why they heck it thought it wasn't deterministic though... bloody Microsoft bugs
MVDBA
January 3, 2020 at 12:39 pm
documentation clearly states that any date conversion to be deterministic needs a deterministic style specifier - '19000101' didn't had it
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply