When you create a computed column in SQL Server, you have the option of flagging it as “persisted”. A persisted computed column is one that is physically stored in the table. If you don’t specify that it’s persisted, then the column’s value will be calculated each time you run a query against it.

You can query the sys.computed_columns system catalog view to find out whether a computed column is marked as persisted.

Example 1 – Checking One Computed Column

Here’s an example that I ran in my test environment. In this case, I’m checking a computed column called TotalValue.

SELECT is_persisted
FROM sys.computed_columns
WHERE name = 'TotalValue';

Result:

+----------------+
| is_persisted   |
|----------------|
| 1              |
+----------------+

In this case the column is persisted.

In case you have multiple computed columns with the same name, you could also add the table name to the WHERE clause:

SELECT is_persisted
FROM sys.computed_columns
WHERE name = 'TotalValue'
AND OBJECT_NAME(object_id) = 'Products';

Example 2 – Return All Computed Columns

In this example I return all computed columns, along with their is_persisted values.

SELECT 
  OBJECT_NAME(object_id) AS [Table],
  name AS [Computed Column],
  is_persisted
FROM sys.computed_columns;

Result:

+----------+-------------------+----------------+
| Table    | Computed Column   | is_persisted   |
|----------+-------------------+----------------|
| Person   | FullName          | 0              |
| Products | TotalValue        | 1              |
+----------+-------------------+----------------+

Example 3 – Include the Schema

In this example I join with the sys.objects view to include the schema in the results.

SELECT 
  SCHEMA_NAME(o.schema_id) AS [Schema],
  OBJECT_NAME(cc.object_id) AS [Table],
  cc.name AS [Computed Column],
  cc.is_persisted
FROM sys.computed_columns cc
INNER JOIN sys.objects o
ON o.object_id = cc.object_id;

Result:

+----------+----------+-------------------+----------------+
| Schema   | Table    | Computed Column   | is_persisted   |
|----------+----------+-------------------+----------------|
| dbo      | Person   | FullName          | 0              |
| dbo      | Products | TotalValue        | 1              |
+----------+----------+-------------------+----------------+