1

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.

Chandra Mohan
  • 729
  • 2
  • 10
  • 29
  • I've never heard about a non-determinstic datatype. Seems like what you are doing is pretty much the same as the checkum-function though: https://msdn.microsoft.com/en-us/library/ms189788.aspx – Lorentz Vedeler Aug 02 '16 at 14:17
  • If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. We do not tolerate this :) – Chandra Mohan Aug 02 '16 at 14:19

1 Answers1

0

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))
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • I did as follow from ur suggesion . `alter table dbo.Table1 add HashKey AS CAST(hashbytes('MD5', PID+PNumber+CONVERT(VARCHAR(50),cast(datestamp as binary))+CONVERT(VARCHAR(50), TransactionCount)+OCD+ONbr+TransactionID) AS VARBINARY(80)) PERSISTED` Its working now. Thx – Chandra Mohan Aug 02 '16 at 14:34
  • Actually i am facing problem now to get the c# Equivalent value for the hash bytes generated value. As we have converted datestamp to binary and then to varchar. Now i am unable to get the c# equivalent value for the same. Any help would be appreciated.Here i have posted as another question http://stackoverflow.com/questions/38739141/convert-sql-hash-bytes-binary-value-multiple-datatype-fields-combination-to-c – Chandra Mohan Aug 03 '16 at 11:45
  • 2
    The reason casting to `BINARY` worked but `VARCHAR` didn't is because `CAST` from a datetime to a string is _locale_-specific. Hence the non-determinism warning; one can change the locale of Windows or SQL Server at any time. Use `CONVERT(VARCHAR(100),datestamp,126)` to get a standardized ISO-8601 format string, and then do the same on the C# application, and your hashes should match (assuming the other fields are all character strings with the same encoding). – rmalayter Aug 17 '17 at 03:10