Changing all computed columns to persisted

Question

I have the following query to check which columns of my database are computed and not persisted

select t.name, c.name
from sys.tables t
inner join sys.computed_columns c on c.object_id = t.object_id
where c.is_persisted = 0

Is there any easy query to update all these columns to persisted?

For sure. The message was: *Ad hoc updates to system catalogs are not allowed.* — Nizam, Aug 15 '14 at 19:03
I just googled that error message and saw lots of results for how to get around it.. — Tab Alleman, Aug 15 '14 at 19:07
@Tab PLEASE do not recommend to people to update system catalogs directly. Do you really think that update would magically write all of those values to disk anyway? No. If you find a way around it, all you're likely to do is corrupt your database, invalidate your support, and a host of other things. Are you wearing Bad Idea Jeans today? — Aaron Bertrand, Aug 15 '14 at 19:10
Also note that not all computed columns *can* be persisted, and in addition to changing them to persisted, you'd also probably have to drop and re-create any constraints or indexes they're involved in... — Aaron Bertrand, Aug 15 '14 at 19:12
Couldn't you just find all the persisted columns, and use the 'ALTER COLUMN ColumnName ADD PERSISTED' alter table command? http://msdn.microsoft.com/en-us/library/ms190273.aspx — Drew Leffelman, Aug 15 '14 at 19:14
@user1948904 See my comment right above yours - it probably isn't going to be that simple, and besides, they are probably looking for the syntax and also how to automate it so they can change them all in one shot instead of one at a time. "Just write the code to do that" doesn't help much if you don't know how to write the code, which I assume is why they're here asking... — Aaron Bertrand, Aug 15 '14 at 19:15
@Aaron, I was trying to suggest dynamically creating the script from the DMVs, but it didn't come across right. I just tested doing ALTER TABLE [dbo].[DatabaseFileSize] ALTER COLUMN [PctFree] DROP PERSISTED; on a test database and then ALTER TABLE [dbo].[DatabaseFileSize] ALTER COLUMN [PctFree] ADD PERSISTED; on a test data base and it did exactly what you think it would do. But I agree with you, probably not a good idea to just do it to everything, as there are a lot of reasons computed columns can't be persisted (for example if there was a getdate() in the definition of the column. — Drew Leffelman, Aug 15 '14 at 19:17
@user1948904 it worked this way. I think it can be improved to treat non-deterministic columns and foreign keys. But it worked for almost all my columns here. Thanks and if you answer with this suggestion, I will be glad to accept it. — Nizam, Aug 15 '14 at 19:28
@Aron, thanks for all very pertinent and helpful comments. And LOL - *Are you wearing Bad Idea Jeans today?* — Nizam, Aug 15 '14 at 19:29

score 2 · Accepted Answer · answered Aug 15 '14 at 19:33

You could use the query you have above to create a script that does something like:

SELECT schemas.name
     , all_objects.name
     , 'ALTER TABLE ' + QUOTENAME( schemas.name ) + '.' + QUOTENAME( all_objects.name ) + ' ALTER COLUMN ' + QUOTENAME( computed_columns.name ) + ' ADD PERSISTED;'
 FROM sys.schemas
 INNER JOIN sys.all_objects
    ON schemas.schema_id = all_objects.schema_id
 INNER JOIN sys.computed_columns
    ON all_objects.object_id = computed_columns.object_id
   AND computed_columns.is_persisted = 0;

The alter table definition can be found http://msdn.microsoft.com/en-us/library/ms190273.aspx.

Be aware of the limitations of persisted columns (non-deterministic functions, etc) could very well cause part of the script to fail.

Changing all computed columns to persisted

1 Answers1