Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change All Computed Columns to Persisted in SQL Server


By:   |   Read Comments (5)   |   Related Tips: More > T-SQL

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:

Msg 4936, Level 16, State 1, Line 16
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 COLUMNPROPERTY function:

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.table1'), N'Now', N'IsDetermistic');

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:

Msg 1935, Level 16, State 1, Line 1
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 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


Last Update:






About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, March 31, 2016 - 4:08:36 PM - Daniel Odulo Back To Top

 I'm running into the same issue as Jimi, and have been unable to find a solution.

"Computed column 'AvailQty' in table 'Inventory' cannot be persisted because the column does user or system data access."

Your response would be greately apreciated.

My UDF add up the quantities in open purchase orders, and I am tryting to persist it in the products table to have the quantity on order available. Without it being persisted, it adds significant processing time to any queries referencing this column!

 


Sunday, November 29, 2015 - 4:38:12 PM - Jimi Jegonia Back To Top

Hi Aaron,

 

I already added 'WITH SCHEMABINDING' in my UDF yet when I used it in computed column it gives an error:

ALTER TABLE dbo.Inventory ADD AvailQty AS ((coalesce([InitQty],(0))-[dbo].[udf_Get_iTotalSoldQty]([InventoryUID]))) PERSISTED;

 

Msg 4934, Level 16, State 3, Line 1
Computed column 'AvailQty' in table 'Inventory' cannot be persisted because the column does user or system data access.

Any idea?

BTW, I am using SQL 2012.

 

Thanks.

 


Friday, September 19, 2014 - 10:44:15 AM - Aaron Bertrand Back To Top

Nice catch Thomas, and sorry for the error. Indeed you are right and the correction has been made.


Friday, September 19, 2014 - 6:43:00 AM - Tom Groszko Back To Top

Persisted columns also have an additional cost of physical I/O when you read them and physical memory while those blocks of data stay in memory.


Friday, September 19, 2014 - 5:21:04 AM - Thomas Franz Back To Top

Thanks for this very useful tip, but should there not be a

OR t.uses_ansi_nulls = 0

instead of

OR t.uses_ansi_nulls = 1

in the last inverse statment (all the columns that aren't currently persisted, and can't be persisted for one or more of the above reasons) plus the error text should be 'Table does not use ansi_nulls'?


Learn more about SQL Server tools