Cant create persisted computed column on a table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Cant create persisted computed column on a...

Cant create persisted computed column on a table

Rate Topic

Display Mode

Topic Options

Author

Message

Harish Ajabe

Posted Wednesday, February 20, 2013 12:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 25, 2013 6:34 AM
Points: 186, Visits: 160

Hi Everyone,

I am trying to add a computed column as -
alter table MyTable add ComputeCol1 AS (Calc(col1) persisted

When I executed I am getting error message -

Msg 4934, Level 16, State 3, Line 1
Computed column 'ComputeCol1' in table 'MyTable' cannot be persisted because the column does user or system data access.

Here a function "Calc" is accessing data from some other table. From some blogs I come to know that I cannot create persisted column using function which is accessing data from user table. But as per requirement I wanted to create computed column this way. Is there any workaround for this so I could execute above DDL query?

Post #1421940

Bhuvnesh

Posted Wednesday, February 20, 2013 12:23 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

Harish Ajabe (2/20/2013)

But as per requirement I wanted to create computed column this way. Is there any workaround for this so I could execute above DDL query?

If possible , apply the function's logic in column's definition.

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1421944

joeroshan

Posted Wednesday, February 20, 2013 12:41 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 574, Visits: 1,176

You cannot create computed columns that refernces other tables

http://msdn.microsoft.com/en-in/library/ms191250(v=sql.105).aspx

You might want to create a trigger to implement this, or if you are in the design phase, try to incorporate this in your application logic (or stored proc ) when you insert / update

-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups

Post #1421952

ALZDBA

Posted Wednesday, February 20, 2013 12:44 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 6,866, Visits: 8,071

is the data for your computed column considered static ?
if yes, use an insert trigger that gets that data and puts is in a regular column.

if no,
How about using a view ?

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #1421954

Harish Ajabe

Posted Wednesday, February 20, 2013 7:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 25, 2013 6:34 AM
Points: 186, Visits: 160

My function is deleterministic.. I am accessing data from user table inside function and thats the problem (function is true for IsUserData objectproperty)..

So seems I have only option to create trigger instead of computed column w00t

Thanks for all your suggestions BigGrin

Post #1422068

« Prev Topic | Next Topic »

Permissions