{"id":317,"date":"2012-10-09T16:13:46","date_gmt":"2012-10-09T23:13:46","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=317"},"modified":"2013-05-30T10:41:48","modified_gmt":"2013-05-30T17:41:48","slug":"computed-column-with-a-date-computed-column-cannot-be-persisted-because-the-column-is-non-deterministic","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/10\/computed-column-with-a-date-computed-column-cannot-be-persisted-because-the-column-is-non-deterministic\/","title":{"rendered":"Computed Column With A Date: Computed column cannot be persisted because the column is non-deterministic"},"content":{"rendered":"<p>Here's a fast one I just stumbled across.\u00a0 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.\u00a0 (Yes!\u00a0 I just managed another pop culture reference in an otherwise dry and lifeless technical blog post!\u00a0 Stay tuned till the end of the article where I'll drop in a pic from Groundhog Day with a witty SQL DB caption.)<!--more--><\/p>\n<p>So you're adding a computed column (aka calculated column, aka persisted column).\u00a0 Your code probably looks something like this (but without the awesome table name)<\/p>\n<pre>if object_id('GroundHogDayTable', 'U') is not null\r\nbegin\r\n \u00a0drop table GroundHogDayTable\r\nend\r\n\r\nCREATE TABLE GroundHogDayTable \u00a0\u00a0\u00a0 \r\n( \u00a0\u00a0\u00a0\u00a0\u00a0 \r\nTheName VARCHAR(100) \u00a0\u00a0\u00a0 \r\n, TheDate DATE \u00a0\u00a0\u00a0 \r\n, Column1 AS \r\n    CONVERT(BIGINT, HASHBYTES('SHA1',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TheName\r\n                                  + CONVERT(VARCHAR(10), theDate))\r\n             )\r\n \u00a0\u00a0  PERSISTED\r\n)<\/pre>\n<p>But, when you attempt to create the the table with the persisted column you get the error message:<\/p>\n<p><span style=\"color: #ff0000;\">Msg 4936, Level 16, State 1, Line 6 Computed column 'Column1' in table 'GroundHogDayTable' cannot be persisted because the column is non-deterministic.<\/span><\/p>\n<p style=\"padding-left: 30px;\"><em>Note: The world's easiest fix to this problem can be had by simply removing the \"PERSISTED\" syntax.\u00a0 Bing! (that's a Groundhog day reference by the way, attribution at the end of this post.)<\/em><\/p>\n<p>Uh, what?\u00a0 A quick review of the columns included in my computed column reveals they are all in fact deterministic.\u00a0 Wait, maybe I don't know what deterministic means?\u00a0 According to the MSDN article \"<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms178091.aspx\" target=\"_blank\">Deterministic and Nondeterministic Functions<\/a>\"<\/p>\n<ul>\n<li>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.<\/li>\n<li>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.<\/li>\n<\/ul>\n<p>Looking through my code again, everything looks fine.\u00a0 A couple of google searches led to a confusingly written stackoverflow post \"<a href=\"http:\/\/stackoverflow.com\/questions\/7315516\/computed-column-month-in-table-cannot-be-persisted-because-the-column-is-non-d\" target=\"_blank\">Computed column 'Month' in table cannot be persisted because the column is non-deterministic<\/a>\".\u00a0 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.\"<\/p>\n<p>Wait a sec!\u00a0 Now we're on to something!\u00a0 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.\u00a0 The easy fix, that let's you keep the column PERSISTED?<\/p>\n<p>Change this:<\/p>\n<pre>CREATE TABLE GroundHogDayTable     \r\n(       \r\nTheName VARCHAR(100)     \r\n, TheDate DATE     \r\n, Column1 AS \r\n    CONVERT(BIGINT, HASHBYTES('SHA1',\r\n                                  TheName\r\n                                  + CONVERT(VARCHAR(10)\r\n, theDate))\r\n             )\r\n     PERSISTED\r\n)<\/pre>\n<p>To this:<\/p>\n<pre>CREATE TABLE GroundHogDayTable     \r\n(       \r\nTheName VARCHAR(100)     \r\n, TheDate DATE     \r\n, Column1 AS \r\n    CONVERT(BIGINT, HASHBYTES('SHA1',\r\n                                  TheName\r\n                                  + CONVERT(VARCHAR(10)\r\n                                  , theDate, <span style=\"background: #00ff00;\"> 101 <\/span>))\r\n             )\r\n     PERSISTED\r\n)<\/pre>\n<p>Bing!<\/p>\n<p><em>(Ned: Phil? Hey, Phil? Phil! Phil Connors? Phil Connors, I thought that was you!<\/em><br \/>\n<em> Phil: Hi, how you doing? Thanks for watching.<\/em><br \/>\n<em> [Starts to walk away]<\/em><br \/>\n<em> Ned: Hey, hey! Now, don't you tell me you don't remember me because I sure as heckfire remember you.<\/em><br \/>\n<em> Phil: Not a chance.<\/em><br \/>\n<em> 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?<\/em><br \/>\n<em> Phil: Ned Ryerson?<\/em><br \/>\n<em> Ned: Bing!<\/em><br \/>\n<em> Phil: Bing.<\/em><br \/>\n<em>Thanks to <a href=\"http:\/\/www.imdb.com\/title\/tt0107048\/quotes?qt=qt0278129\">IMDB<\/a>)<\/em><\/p>\n<address>\u00a0<\/address>\n<figure id=\"attachment_324\" aria-describedby=\"caption-attachment-324\" style=\"width: 448px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-324\" title=\"columnIsNon-deterministic\" alt=\"\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/10\/columnisnon-deterministic.jpg\" width=\"448\" height=\"250\" srcset=\"http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/10\/columnisnon-deterministic.jpg 448w, http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/10\/columnisnon-deterministic-300x167.jpg 300w\" sizes=\"auto, (max-width: 448px) 100vw, 448px\" \/><figcaption id=\"caption-attachment-324\" class=\"wp-caption-text\">Neddle Nose Ned? Ned The Head?\u00a0Non-Deterministic Ned!?! Bing!<\/figcaption><\/figure>\n<address>\u00a0<\/address>\n<address>\u00a0<\/address>\n","protected":false},"excerpt":{"rendered":"<p>Here's a fast one I just stumbled across.\u00a0 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.\u00a0 (Yes!\u00a0 I just managed another pop culture reference in an otherwise dry and lifeless technical blog post!\u00a0 Stay tuned till [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,43,57,72],"tags":[74,75,77,76,78,73,10,79,9,12,11,173,174],"class_list":["post-317","post","type-post","status-publish","format-standard","hentry","category-data-architect","category-microsoft-sql-server","category-t-sql","category-table-design","tag-bill-murray","tag-bing","tag-calculated-column","tag-computed-column","tag-deterministic","tag-groundhog-day","tag-ms-sql","tag-non-deterministic","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql","tag-table-design"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/317","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=317"}],"version-history":[{"count":16,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/317\/revisions"}],"predecessor-version":[{"id":325,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/317\/revisions\/325"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=317"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}