问题描述
<$ 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!