This article demonstrates how to use T-SQL to create a computed column in SQL Server.

A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.

Example 1 – Create a Table with a Computed Column

Here’s an example of creating a table with a computed column, inserting data, then selecting the table’s contents.

CREATE TABLE Products
  (
    ProductID int IDENTITY (1,1) NOT NULL,
    ProductName varchar(255), 
    Quantity smallint,
    Price money,
    TotalValue AS Quantity * Price
  );

INSERT INTO Products (ProductName, Quantity, Price)
VALUES 
  ('Hammer', 5, 10),
  ('Corkscrew', 2, 7.50),
  ('Kettle', 3, 25.15);

SELECT *
FROM Products;

Result:

+-------------+---------------+------------+---------+--------------+
| ProductID   | ProductName   | Quantity   | Price   | TotalValue   |
|-------------+---------------+------------+---------+--------------|
| 1           | Hammer        | 5          | 10.0000 | 50.0000      |
| 2           | Corkscrew     | 2          | 7.5000  | 15.0000      |
| 3           | Kettle        | 3          | 25.1500 | 75.4500      |
+-------------+---------------+------------+---------+--------------+

In this case, the last column is a computed column. It multiplies the quantity column by the price column. This enables us to get a total value of the product currently in stock.

Example 2 – Create a Persisted Computed Column

You can create a persisted computed column by adding the PERSISTED argument. This will result in the computed value being physically stored in the table. If it’s not persisted, then the value is calculated each time you read the computed column.

Here’s the same code example as the previous one, except this time I create a persisted computed column:

CREATE TABLE Products
  (
    ProductID int IDENTITY (1,1) NOT NULL,
    ProductName varchar(255), 
    Quantity smallint,
    Price money,
    TotalValue AS Quantity * Price PERSISTED
  );

The only difference is the PERSISTED argument.

Example 3 – Update a Value

Now if a value is updated in the Quantity or Price columns, this will affect the total value returned by the computed column. This will occur whether the column is persisted or not.

If somebody buys a hammer for example, this will affect the total value returned by the computed column:

UPDATE Products
SET Quantity = 4
WHERE ProductId = 1;

SELECT *
FROM Products;

Result:

+-------------+---------------+------------+---------+--------------+
| ProductID   | ProductName   | Quantity   | Price   | TotalValue   |
|-------------+---------------+------------+---------+--------------|
| 1           | Hammer        | 4          | 10.0000 | 40.0000      |
| 2           | Corkscrew     | 2          | 7.5000  | 15.0000      |
| 3           | Kettle        | 3          | 25.1500 | 75.4500      |
+-------------+---------------+------------+---------+--------------+