Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cant create persisted computed column on a table


Cant create persisted computed column on a table

Author
Message
Harish Ajabe
Harish Ajabe
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
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?
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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;-)
joeroshan
joeroshan
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1045 Visits: 1987
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
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8839
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 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" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Harish Ajabe
Harish Ajabe
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
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 w00t

Thanks for all your suggestions :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search