带有datetime2字段的HashBytes [英] HashBytes with datetime2 field

问题描述

 <$ c $使用hashbytes创建一个散列键以获得性能增益，现在我们在where子句中使用它。 c> 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 
  

但是其中一列是一个datetime2字段，我无法添加。虽然我正在尝试获取低于错误消息，但由于该列是非确定性的，因此无法持久保存表'table1'中的计算列'HashKey'。

/ p>

从我的研究中我发现datetime2不能被使用，因为它是非确定性的。

但我无法改变格式，因为我需要比较的价值，因为它是包括所有毫秒。

任何人都可以请给我一个解决办法？任何替代解决方案将不胜感激。 / p>

解决方案

我不确定是否有影响..

以二进制总是给出新值。请参阅下面的示例..

  select getdate（），cast（getdate（）as binary） 
 
 2016-08-02 10：17：20.573 0x000000000000000000000000000000000000000000000000A65600A98EEC 
 
 
 2016-08-02 10：17：40.537 0x000000000000000000000000000000000000000000000000A65600A9A651 
  

  select hashbytes（'md5'，cast（getdate（）as binary））
  

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

这篇关于带有datetime2字段的HashBytes的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！