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?

Change this:

CREATE TABLE GroundHogDayTable     
(       
TheName VARCHAR(100)     
, TheDate DATE     
, Column1 AS 
    CONVERT(BIGINT, HASHBYTES('SHA1',
                                  TheName
                                  + CONVERT(VARCHAR(10)
, theDate))
             )
     PERSISTED
)

To this:

CREATE TABLE GroundHogDayTable     
(       
TheName VARCHAR(100)     
, TheDate DATE     
, Column1 AS 
    CONVERT(BIGINT, HASHBYTES('SHA1',
                                  TheName
                                  + CONVERT(VARCHAR(10)
                                  , theDate,  101 ))
             )
     PERSISTED
)

Bing!

(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?
Ned: Bing!
Phil: Bing.
Thanks to IMDB)