Does a persisted computed column need to be deterministic?
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?
Best Answer
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.
Note that CLR functions cannot necessarily be found by the engine to be deterministic.
