Change All Computed Columns to Persisted in SQL Server

Problem

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.

Solution

A column can be made persisted through DML, in spite of what the documentation states, where you’ll find the following relevant parts:

ALTER COLUMN

  The modified column cannot be any one of the following:

    A computed column or used in a computed column.

In fact, 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()
);

The column 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:

Thankfully, you can discover whether a column is deterministic using the COLUMNPROPERTY function:

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:

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 PRINT command, but that is not because the variable is somehow truncated. This still should allow you to validate that the code outputs the DDL commands you roughly expect, before blindly executing them. Note that some of these commands may still fail for unforeseen reasons, but we’ve eliminated any that will fail for the most common ones.

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
);

Conclusion

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.

Next Steps

• Review the following tips and other resources:
  • Using Computed Columns in SQL Server with Persisted Values
  • How to create indexes on computed columns in SQL Server
  • Getting creative with Computed Columns in SQL Server
  • Deterministic and Nondeterministic Functions (MSDN)
  • COLUMNPROPERTY (Transact-SQL) (MSDN)
  • Indexes on Computed Columns (MSDN)