In trying to add a computed column to a SQL Server table, I've found that casting a column with a DATE type directly to a VARCHAR is considered non-deterministic. However, If I pull out the individual parts of the date and cast them individually then everything is fine. I can't think of a reasonable explaination for why the cast directly from DATE to VARCHAR would be non-deterministic. Does anyone have an explanation?
Ex.
create table [dbo].[junk_CCtest]
(
PatientId bigint identity not null,
EmployerId varchar(6) default 'F*Corp',
EffDate date default getdate()
)
go
-- This works fine.
alter table dbo.junk_CCtest
add Checksum1 as (hashbytes('sha2_256', EmployerId + '/' + cast(PatientId as varchar(10)) + cast(year(EffDate) as varchar(4)) + cast(month(EffDate) as varchar(2)) + cast(day(EffDate) as varchar(2)))) persisted;
go
-- This results in: "Computed column 'Checksum3' in table 'junk_CCtest' cannot be persisted because the column is non-deterministic."
alter table dbo.junk_CCtest
add Checksum3 as (hashbytes('sha2_256', EmployerId + '/' + cast(PatientId as varchar(10)) + cast(EffDate as varchar(10)))) persisted;
go
Thanks,
Ian
You can store date in a VARCHAR column, you can also store non-dates in that VARCHAR column.
We can convert the Date into Datetime in two ways. Using CONVERT() function: Convert means to change the form or value of something. The CONVERT() function in the SQL server is used to convert a value of one type to another type. Convert() function is used to convert a value of any type to another datatype.
UPDATE: DATEPART(weekday) is non-deterministic because it relies on DATEFIRST (source).
The string (varchar) representation of a date depends on your "locale" settings (e.g. dates in UK are often represented differently than in the US).
In your example above, your first CAST() explicitly specifies the format of the varchar, but the second one forces the database to examine its locale settings to determine how to format the varchar result.
The simple fact that the conversion depends on something external to the CAST() function makes it non-deterministic.
In other words, you run the CAST() with one locale setting, change the locale then run the SAME CAST() again, and you get a different result. This is the definition of non-deterministic behavior.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With