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?

more ▼

asked Oct 11 '09 at 03:25 PM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
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.

more ▼

answered Oct 11 '09 at 07:44 PM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left

Note that CLR functions cannot necessarily be found by the engine to be deterministic.


more ▼

answered Oct 16 '09 at 08:45 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 11 '09 at 03:25 PM

Seen: 4294 times

Last Updated: Oct 11 '09 at 03:25 PM