Creating a Persisted Computed Column with a function

by Paul   Last Updated April 20, 2018 17:06 PM

I am working with the programmers on a database solution. They want to add a computed column to mimic the old keys for the older queries, procedures, and systems and index it. The new keys will be GUIDS.

To do this, they want to create a function for the computed column that creates a value and persist it. It will not let them persist the column. I don't have any warm fuzzies about the idea and I also can not find any info on the web about the technique (is it a technique?).

I am thinking they need to add a trigger instead. Does anyone have any ideas?



Answers 2


You can read about Persisted Computed Columns in BOL, and the related Indexes on Computed Columns.

There are restrictions on the expression you can use in a persisted computed column. The Expression "must be deterministic when PERSISTED is specified."

David Browne - Microsoft
David Browne - Microsoft
April 20, 2018 16:48 PM

I'm not sure why you think you need a function or a computed column to do this. You can just add a new column to the table with a default value and index it however you want.

CREATE TABLE dbo.whatever ( Id INT );

ALTER TABLE dbo.whatever
ADD YourMom UNIQUEIDENTIFIER
        DEFAULT NEWSEQUENTIALID();

CREATE INDEX ix_whatever ON dbo.whatever (YourMom);

Hope this helps!

sp_BlitzErik
sp_BlitzErik
April 20, 2018 16:53 PM

Related Questions



Use An Aggregate With Computed Column

Updated April 29, 2017 08:06 AM


When are computed columns computed?

Updated April 27, 2016 09:02 AM

Referencing another table from a computed column

Updated November 27, 2017 22:06 PM