question

Matt Whitfield avatar image
Matt Whitfield asked

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?

database-designstorageddlengine
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve Jones - Editor avatar image
Steve Jones - Editor answered

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

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.