Chandra Mohan Chandra Mohan - 5 months ago 48
SQL Question

HashBytes with datetime2 field

I am creating a hash key using hashbytes on multiple columns to get performance gain which we are using right now in where clause.

alter table dbo.Table1
add HashKey AS CAST(hashbytes('MD5', PID+PNumber+CONVERT([varchar] (50),[DateStamp]) +CONVERT(VARCHAR(50), TransactionCount)+OCD+ONbr+TransactionID) AS VARBINARY(80)) PERSISTED

But one of the column in that is a datetime2 field which i am unable to add. While i was trying i am getting below error message

"Computed column 'HashKey' in table 'table1' cannot be persisted because the column is non-deterministic.".

From my research i found that datetime2 cannot be used as it is non-deterministic.

But i cannot change the format as i need to compare the value exactly as it is including all milliseconds.

Can anybody please give me a work around?.Any alternate solution will be appreciated.


I am not sure of implications..

But casting datetime to binary always gives new value.see below for Example..

select getdate(),cast(getdate()as  binary)

2016-08-02 10:17:20.573 0x000000000000000000000000000000000000000000000000A65600A98EEC

2016-08-02 10:17:40.537 0x000000000000000000000000000000000000000000000000A65600A9A651

so try like..

select hashbytes('md5',cast(getdate()as  binary))