HashBytes with datetime2 field

Question

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.

Answer 1

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))