Tuesday, September 30, 2014

Changing computed columns columns to persisted - the easy way

In this morning's SQL Server Central newsletter, there was a link to an article from Aaron Bertrand about how to change whether a computed column is persisted or not. He presented a T-SQL method for doing so. Here, I'll present a powershell method for doing the exact same thing.

import-module sqlps;

$s = new-object microsoft.sqlserver.management.smo.server '.';
$db = $s.Databases['AdventureWorks2014'];

foreach ( $tbl in $db.Tables ) {
    foreach ( $col in $tbl.Columns ) {
        if ( $col.Computed -and (! $col.IsPersisted) -and $col.IsDeterministic ) {
            $col.IsPersisted = $true;
            $col.Alter();
        }
    }
}
Seriously, that's it. If you wanted to be fancy (or just want a chance to review the script before executing it), just use ScriptAlter() instead of Alter() and pass a ScriptingOptions object it. I leave this as an exercise to the reader. With nothing against Aaron, I find the above to be much more readable. To quote Larry Wall "TMTOWTDI", though. So pick your favorite and go with it.

I'll be giving a presentation at SQL Saturday #332 on just this sort of thing. If you want to learn a little powershell yourself, consider attending my session!

No comments:

Post a Comment