When are computed columns computed?

Question

When are the values for computed columns determined?

• When the value is retrieved?
• When the value is changed?
• Some other time?

I'm guessing this is a novice question since I'm not finding anything in my searches.

Answer 1

It depends on how you define the computed column. A PERSISTED computed column will be calculated and then stored as data inside the table. If you do not define the column as PERSISTED, it will be calculated when your query is run.

Please see Aaron's answer for a great explanation and proof.

Pinal Dave also describes this in detail and shows proof of storage in his series:

SQL SERVER – Computed Column – PERSISTED and Storage

Answer 2

This is very easy to prove on your own. We can create a table with a computed column that uses a scalar user-defined function, and then check plans and function stats before and after both an update and select, and see when an execution gets recorded.

Let's say we have this function:

CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT 'XX' + SUBSTRING(@x, 3, LEN(@x)-4) + 'XXXX');
END
GO

And this table:

CREATE TABLE dbo.Floobs
(
  FloobID int IDENTITY(1,1),
  Name varchar(32),
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)),
  CONSTRAINT pk_Floobs PRIMARY KEY(FloobID),
  CONSTRAINT ck_Name CHECK (LEN(Name)>=8)
);
GO

Let's check sys.dm_exec_function_stats (new in SQL Server 2016 and Azure SQL Database) before and after an insert, and then after a select:

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

INSERT dbo.Floobs(Name) VALUES('FrankieC');

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

SELECT * FROM dbo.Floobs;

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

I see no function call on the insert, only on the select.

Now, drop the tables and do it again, this time changing the column to PERSISTED:

DROP TABLE dbo.Floobs;
GO
DROP FUNCTION dbo.mask;
GO

...
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)) PERSISTED,
...

And I see the opposite happening: I get an execution logged on the insert, but not on the select.

Don't have a modern enough version of SQL Server to use sys.dm_exec_function_stats? No worries, this is captured in the execution plans, too.

For the non-persisted version, we can see the function referenced only in the select:

While the persisted version only shows the computation happening on insert:

Now, Martin brings up a great point in a comment: this isn't always going to be true. Let's create an index that doesn't cover the persisted computed column, and run a query that uses that index, and see if the lookup gets the data from the existing persisted data, or computes the data at runtime (drop and re-create function and table here):

CREATE INDEX x ON dbo.Floobs(Name);
GO

INSERT dbo.Floobs(name) 
  SELECT LEFT(name, 32) 
  FROM sys.all_columns 
  WHERE LEN(name) >= 8;

Now, we'll run a query that uses the index (actually it uses the index by default in this specific case anyway, even without a where clause):

SELECT * FROM dbo.Floobs WITH (INDEX(x))
  WHERE Name LIKE 'S%';

I see additional executions in function stats, and the plan doesn't lie:

So, the answer is IT DEPENDS. In this case, SQL Server thought it would be cheaper to re-compute the values than to perform lookups. This could change due to a variety of factors, so don't rely on it. And this can happen in either direction whether or not a user-defined function is used; I only used it here because it made it that much easier to illustrate.

Answer 3

The answer to this question truly is "it depends." I have just run across an example where SQL Server is using the index on the persisted computed column but it is still performing the function, as if the values were never persisted to begin with. It may have to do with the data type of the column (nvarchar(37)) or possibly the size of the table (about 7 million rows), but SQL Server decided to ignore the persisted keyword, it appears, in this particular instance.

In this case, the primary key on the table is TransactionID which is also a computed and persisted column. The execution plan is generating an index scan and in a table with only 7 million rows this simple query is taking upwards of 2-3 minutes to run because the function is run again over every row and the values don't appear to be persisted in the index.