A site seeder question: If I was to create a persisted computed column on a table, would the expression for that computed column need to be deterministic?
asked Oct 11, 2009 at 03:25 PM in Default
Matt Whitfield ♦♦
You get an error if you try it!
CREATE TABLE [dbo].[NonDeterministicTable] ( [MyID] [int] IDENTITY(1, 1), Name VARCHAR(10) NOT NULL, [Age] AS RAND()*100 PERSISTED ) GO
Msg 4936, Level 16, State 1, Line 1 Computed column 'Age' in table 'NonDeterministicTable' cannot be persisted because the column is non-deterministic.
A deterministic expression is one that always returns the same result for a specified set of inputs. To be reckoned to be deterministic, all functions that are referenced by the expression must be deterministic and precise (a persisted column may not be precise (e.g. a float). One thing that often catches people out is when they try to do date-based calculations using GetDate() and then try to make the column persistent. Oh no. GetDate() is not deterministic.
answered Oct 11, 2009 at 07:44 PM
Note that CLR functions cannot necessarily be found by the engine to be deterministic.
answered Oct 16, 2009 at 08:45 PM
Steve Jones - Editor ♦♦