28

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.

18

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

  • 6
    What about if they are persisted but the query plan uses an index that does not cover that column? I'm not sure whether you would get a lookup or if it would just compute it on the fly and can't currently test it. – Martin Smith Apr 27 '16 at 14:14
  • 1
    @Martin you're right, in my test SQL Server chose re-computing over a lookup. – Aaron Bertrand Apr 27 '16 at 14:43
33

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:

enter image description here

enter image description here

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

enter image description here

enter image description here

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:

enter image description here

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.

  • Much appreciated, I never questioned the behavior of the engine in computing results. – Arthur D Apr 27 '16 at 15:07
  • 8
    @ArthurD It is an optimizer decision based (mostly) on the estimated costs of each alternative, see my answer to another question here. – Paul White Apr 27 '16 at 15:08
-1

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.

creating table with persisted column execution plan showing function is being performed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.