February 20, 2013 at 12:14 am
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?
February 20, 2013 at 12:23 am
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;-)
February 20, 2013 at 12:41 am
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
February 20, 2013 at 12:44 am
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
February 20, 2013 at 7:09 am
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 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply