CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
CarCrazyBen
CarCrazyBen

Reputation: 1136

having trouble with SQL Server Computed Column

I am working on a web application with a Javascript Backend developer. We thought it would be useful to add a computed column to one of our tables to effectively act as a Unique ID per "event".

I have tried applying HASHBYTES to a concatenation of a timestamp and a User ID field, which works well in a Select statement. However, when altering my Table with a Persisted (computed) column I receive the error:

computed column {name} in table 'myTable' cannot be persisted because the column is non-deterministic.

I am not entirely clear what this error is telling me. Is it related to using a datetimeoffset field in my calculation? Or am I trying to use hashbytes in an incorrect manner? Is there a better way to compute and store a UUID?

Upvotes: 1

Views: 173

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15658

Are you relying on implicit conversions to generate the computed column? Try to get CONVERT function involved. Hashing a timestamp column is not deterministic and the CONVERT function can help make it deterministic if used as shown below.

Select hashbytes('MD5', convert(varchar(200),(CONVERT(varchar(10),datestamp,126)+' '+CONVERT(VARCHAR(24),datestamp,114)),2)) from Events Where DateStamp ='2016-06-30 12:19:35.257961'

Upvotes: 1

Related Questions