Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is a cast from DATE to VARCHAR non-deterministic?

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

like image 915
Ian Lee Avatar asked Oct 07 '16 16:10

Ian Lee


People also ask

Can date be stored as varchar?

You can store date in a VARCHAR column, you can also store non-dates in that VARCHAR column.

Can you cast date to datetime in SQL?

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.

Is Datepart deterministic?

UPDATE: DATEPART(weekday) is non-deterministic because it relies on DATEFIRST (source).


1 Answers

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.

like image 74
SlimsGhost Avatar answered Oct 08 '22 00:10

SlimsGhost