x

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, 2009 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 newest

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.

more ▼

answered Oct 11, 2009 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.

http://msdn.microsoft.com/en-us/library/ms191250.aspx

more ▼

answered Oct 16, 2009 at 08:45 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x107
x33
x30
x5

asked: Oct 11, 2009 at 03:25 PM

Seen: 4601 times

Last Updated: Oct 11, 2009 at 03:25 PM