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