Cant create persisted computed column on a table

Question

Cant create persisted computed column on a table

Harish Ajabe

Ten Centuries

Points: 1178
More actions
February 20, 2013 at 12:14 am

#394223

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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 SSChampion Points: 10377 More actions · Answer 2

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[/url]

Johan Bijnens SSC Guru Points: 135253 More actions · Answer 3

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

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

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

- 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

Harish Ajabe Ten Centuries Points: 1178 More actions · Answer 4

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 😀