Change All Computed Columns to Persisted in SQL Server

By:   |   Updated: 2014-09-19   |   Comments (8)   |   Related: More > T-SQL

Building Better SQL Server Queries and Working with Data

Free MSSQLTips Webinar: Building Better SQL Server Queries and Working with Data

Attend this webinar to learn how to write T-SQL code and improve the performance of your queries along with how to import and export data with SQL Server.


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.

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

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

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

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'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:

  RETURN (SELECT @id + 1);

WITH SCHEMABINDING -- only difference
  RETURN (SELECT @id + 1);

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:

  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:

CREATE TABLE dbo.table3(ID INT, comp AS (ID+1));
CREATE TABLE dbo.table4(ID INT, comp AS (ID+1));

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.


SELECT @sql += N'
  + QUOTENAME( + '.' 
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],, N'IsIndexable') = 1
AND COLUMNPROPERTY(t.[object_id],, N'IsDeterministic') = 1
AND t.uses_ansi_nulls = 1
AND c.is_computed = 1
  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):

  [schema] =,
  [table]  =,
  [column] =,
  Reason = '' + CASE
    WHEN COLUMNPROPERTY(t.[object_id],, N'IsIndexable') = 0
 THEN 'Not indexable | ' ELSE '' END
    + CASE 
 WHEN COLUMNPROPERTY(t.[object_id],, 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
  SELECT 1 FROM sys.computed_columns
  WHERE [object_id] = t.[object_id]
  AND column_id = c.column_id
  AND is_persisted = 0
  COLUMNPROPERTY(t.[object_id],, N'IsIndexable') = 0
  OR COLUMNPROPERTY(t.[object_id],, 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.

Next Steps

Last Updated: 2014-09-19

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

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 (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Tuesday, January 22, 2019 - 10:16:52 AM - Greg Robidoux Back To Top

Tom and Aaron, the code has been fixed.


Tuesday, January 22, 2019 - 9:39:36 AM - Aaron Bertrand Back To Top

 Thanks Tom, you're right. Sorry about that.

Tuesday, January 22, 2019 - 3:31:36 AM - Tom Back To Top

There is a typo:

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

it should be IsDeterministic

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.




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'?


Recommended Reading

Rolling up multiple rows into a single row and column for SQL Server data

Using MERGE in SQL Server to insert, update and delete at the same time

SQL Server Loop through Table Rows without Cursor

Cursor in SQL Server

How to use @@ROWCOUNT in SQL Server

get free sql tips
agree to terms

Learn more about SQL Server tools