SQL Interview: 9: Computed columns in table definitions

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

Consider the following code:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1)
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    TradingName nvarchar(100) NOT NULL,
    CreatedDate AS SYSDATETIME()
);
GO

INSERT dbo.Customers (TradingName)
VALUES (N'Customer A');
GO

SELECT * FROM dbo.Customers;
GO
SELECT * FROM dbo.Customers;
GO

Will the CreatedDate column return the same value in both SELECT statements?

Answer:

When you define a computed column, the value is calculated when it is SELECTed. In this case, the two SELECT operations will occur at different times, and different values will be returned for that column.

It is possible with some computed columns to add the term PERSISTED. In that case, the value is calculated at INSERT or UPDATE but is then stored. The same value would be returned every time it is SELECTed.

However, in this case, you cannot apply PERSISTED to the computed column, as the expression SYSDATETIME() is not deterministic.

2021-04-13