Change All Computed Columns to Persisted in SQL Server
By: Aaron Bertrand | Comments (8) | Related: More > TSQL
I recently answered a question from a user who wanted to convert all of their SQL Server computed columns to be persisted. This allows you to shift the SQL Server cost of performing the computation from query time to the time of the insert/update, at the cost of storage.
A column can be made persisted through DML, in spite of what the documentation states, where you'll find the following relevant parts:
The modified column cannot be any one of the following:
A computed column or used in a computed column.
ALTER COLUMN can be used to make a lot of computed columns persisted, even though the documentation would lead you to believe otherwise (what you can't do is change things like the name, data type, or the computed column definition). The syntax for making a computed column persisted - while ignoring for now the impact to on-disk structures - is actually quite simple:
ALTER TABLE dbo.tablename ALTER COLUMN columnname ADD PERSISTED;
Oddly enough, as long as the column can be persisted, you can run that DDL 100 times and it will continue happily responding "The command completed successfully."
Making a column non-persisted, too, is equally simple:
ALTER TABLE dbo.tablename ALTER COLUMN columnname DROP PERSISTED;
But there are a few caveats you should know about before attempting this procedure.
Not all computed columns can be persisted. For one, they have to be deterministic, which means given the same input they always return the same output. Consider the following meaningless table:
CREATE TABLE dbo.table1 ( ID INT, Now AS GETDATE() );
Now can not be made persisted, because
GETDATE() is not deterministic - it can return a different value every time you call it. It wouldn't make sense to do so anyway, because that persisted value would become stale and invalid by the time you could read it again. For details on deterministic and non-deterministic functions, see this topic in Books Online. The error message I would receive in the above case:
Computed column 'Now' in table 'table1' cannot be persisted because the column is non-deterministic.
Thankfully, you can discover whether a column is deterministic using the
SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.table1'), N'Now', N'IsDeterministic');
By inspecting those results, we can quickly determine if we should even bother trying to mark a computed column as persisted (or correct the computed column definition).
Another issue is the use of user-defined functions in the computed column definition. Often such columns cannot be marked as persisted because the functions are deemed to be non-deterministic; though this can be defeated for some functions that actually are deterministic by using
WITH SCHEMABINDING. Consider the following two nearly identical functions:
CREATE FUNCTION dbo.udf1(@id INT) RETURNS INT AS BEGIN RETURN (SELECT @id + 1); END GO CREATE FUNCTION dbo.udf2(@id INT) RETURNS INT WITH SCHEMABINDING -- only difference AS BEGIN RETURN (SELECT @id + 1); END GO
There's nothing non-deterministic about the first function, except that it isn't marked as
WITH SCHEMABINDING. Yet if I create the following table:
CREATE TABLE dbo.table2 ( ID INT, comp1 AS dbo.udf1(ID), comp2 AS dbo.udf2(ID) );
If I try to persist these two columns, the first fails with the same 4936 error as above, but the second works just fine. While
IsDeterministic will work here, an additional property we can check for any column we want to persist is whether or not SQL Server thinks that it can be indexed. Once again,
COLUMNPROPERTY to the rescue:
SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.table2', N'comp1', N'IsIndexable'), -- 0 COLUMNPROPERTY(OBJECT_ID(N'dbo.table2', N'comp2', N'IsIndexable'); -- 1
One more issue you may come across:
ANSI_NULLS. Consider the following two tables:
SET ANSI_NULLS ON; GO CREATE TABLE dbo.table3(ID INT, comp AS (ID+1)); GO SET ANSI_NULLS OFF; GO CREATE TABLE dbo.table4(ID INT, comp AS (ID+1)); GO
If I try to make the column
comp persisted, even though both have deterministic computed column definitions, the first table succeeds, but the second table fails with:
Cannot create index. Object 'table4' was created with the following SET options off: 'ANSI_NULLS'.
The error message is a bit misleading, since I'm really not trying to create an index, but the end result is the same: we need to avoid trying to persist any computed columns in tables that were created with
ANSI_NULLS OFF (or we first need to re-create any such tables with
ANSI_NULLS ON - just adding a new column with the setting on isn't enough).
You can run into other, similar issues in certain scenarios if the table or any dependent objects were created with any settings OFF like
QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, etc. To keep things simple, let's assume the above problems are the only issues in scope.
We can put this all together by building dynamic SQL that will give us the DDL to mark all computed columns that we think, based on the above criteria, can be persisted.
DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' ALTER COLUMN ' + QUOTENAME(c.name) + ' ADD PERSISTED;' FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.[object_id] = t.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE COLUMNPROPERTY(t.[object_id], c.name, N'IsIndexable') = 1 AND COLUMNPROPERTY(t.[object_id], c.name, N'IsDeterministic') = 1 AND t.uses_ansi_nulls = 1 AND c.is_computed = 1 AND EXISTS ( SELECT 1 FROM sys.computed_columns WHERE [object_id] = t.[object_id] AND column_id = c.column_id AND is_persisted = 0 ); PRINT @sql; --EXEC sp_executesql @sql;
Depending on the number of columns and the size of your object names, you may not be able to see the entire output of the
To find the inverse (all the columns that aren't currently persisted, and can't be persisted for one or more of the above reasons):
SELECT [schema] = s.name, [table] = t.name, [column] = c.name, Reason = '' + CASE WHEN COLUMNPROPERTY(t.[object_id], c.name, N'IsIndexable') = 0 THEN 'Not indexable | ' ELSE '' END + CASE WHEN COLUMNPROPERTY(t.[object_id], c.name, N'IsDeterministic') = 0 THEN 'Not Deterministic | ' ELSE '' END + CASE WHEN t.uses_ansi_nulls = 0 THEN 'Table does not use ansi_nulls' ELSE '' END FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.[object_id] = t.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE c.is_computed = 1 AND EXISTS ( SELECT 1 FROM sys.computed_columns WHERE [object_id] = t.[object_id] AND column_id = c.column_id AND is_persisted = 0 ) AND ( COLUMNPROPERTY(t.[object_id], c.name, N'IsIndexable') = 0 OR COLUMNPROPERTY(t.[object_id], c.name, N'IsDeterministic') = 0 OR t.uses_ansi_nulls = 0 );
Making columns persisted is kind of like opening Pandora's box - there are a lot of little things that you need to consider, and I have barely touched the surface here. I hope that is a useful start for you; in a future tip I will take a deeper look at the impact of making a column persisted when the table is large or when the column definition references an expensive user-defined function. I'll also see if I can somehow determine from the metadata when a function is not trusted simply because it really is non-deterministic, or if all that is needed is to add the
WITH SCHEMABINDING option.
- Review the following tips and other resources:
About the author
View all my tips