Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cant create persisted computed column on a table Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 12:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 12:32 AM
Points: 186, Visits: 172
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
Posted Wednesday, February 20, 2013 12:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 2,840, Visits: 3,970
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1421944
Posted Wednesday, February 20, 2013 12:41 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 12:43 AM
Points: 630, Visits: 1,290
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
Posted Wednesday, February 20, 2013 12:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1421954
Posted Wednesday, February 20, 2013 7:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 12:32 AM
Points: 186, Visits: 172
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

Thanks for all your suggestions
Post #1422068
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse