Here’s a fast one I just stumbled across. Since, I’m pretty sure I’ve made this mistake before I thought I’d write it down in an effort to prevent myself from enduring another Groundhog Day moment. (Yes! I just managed another pop culture reference in an otherwise dry and lifeless technical blog post! Stay tuned till the end of the article where I’ll drop in a pic from Groundhog Day with a witty SQL DB caption.)
So you’re adding a computed column (aka calculated column, aka persisted column). Your code probably looks something like this (but without the awesome table name)
if object_id('GroundHogDayTable', 'U') is not null begin drop table GroundHogDayTable end CREATE TABLE GroundHogDayTable ( TheName VARCHAR(100) , TheDate DATE , Column1 AS CONVERT(BIGINT, HASHBYTES('SHA1', TheName + CONVERT(VARCHAR(10), theDate)) ) PERSISTED )
But, when you attempt to create the the table with the persisted column you get the error message:
Msg 4936, Level 16, State 1, Line 6 Computed column ‘Column1’ in table ‘GroundHogDayTable’ cannot be persisted because the column is non-deterministic.
Note: The world’s easiest fix to this problem can be had by simply removing the “PERSISTED” syntax. Bing! (that’s a Groundhog day reference by the way, attribution at the end of this post.)
Uh, what? A quick review of the columns included in my computed column reveals they are all in fact deterministic. Wait, maybe I don’t know what deterministic means? According to the MSDN article “Deterministic and Nondeterministic Functions“
- Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.
- Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
Looking through my code again, everything looks fine. A couple of google searches led to a confusingly written stackoverflow post “Computed column ‘Month’ in table cannot be persisted because the column is non-deterministic“. Luckily, I caught sight of the lead-in sentnece in the post marked as the answer “The datename function can return different results dependant on the language of the logged in user hence is not deterministic.”
Wait a sec! Now we’re on to something! I don’t have a datename column, but I do have a date column which I was converting to varchar *and* I had neglected to specify the style. The easy fix, that let’s you keep the column PERSISTED?
CREATE TABLE GroundHogDayTable ( TheName VARCHAR(100) , TheDate DATE , Column1 AS CONVERT(BIGINT, HASHBYTES('SHA1', TheName + CONVERT(VARCHAR(10) , theDate)) ) PERSISTED )
CREATE TABLE GroundHogDayTable ( TheName VARCHAR(100) , TheDate DATE , Column1 AS CONVERT(BIGINT, HASHBYTES('SHA1', TheName + CONVERT(VARCHAR(10) , theDate, 101 )) ) PERSISTED )
(Ned: Phil? Hey, Phil? Phil! Phil Connors? Phil Connors, I thought that was you!
Phil: Hi, how you doing? Thanks for watching.
[Starts to walk away]
Ned: Hey, hey! Now, don’t you tell me you don’t remember me because I sure as heckfire remember you.
Phil: Not a chance.
Ned: Ned… Ryerson. “Needlenose Ned”? “Ned the Head”? C’mon, buddy. Case Western High. Ned Ryerson: I did the whistling belly-button trick at the high school talent show? Bing! Ned Ryerson: got the shingles real bad senior year, almost didn’t graduate? Bing, again. Ned Ryerson: I dated your sister Mary Pat a couple times until you told me not to anymore? Well?
Phil: Ned Ryerson?
Thanks to IMDB)