How to Use SQL Server Computed Column

A computed column is a virtual column whose value is a result of an expression mainly using other data stored in other columns. A computed column is a virtual column meaning SQL Server does not actually store it physically on a table. Instead, the value of a computed column is calculated when the data is queried. You can, however, store it physically by specifying the persisted parameter.

This guide will give you the essentials of creating and using computed columns in SQL Server.

Before we dive into a practical example and use computed columns, there are a few key points to note.

A computed column cannot contain as default, not null, or foreign key constraints unless the column is set to persisted.
You cannot use a computed column on an insert or update statements.

SQL Server Computed Column Usage

To better understand how to use the SQL Server computed columns, we will use a practical example.

Start by creating a sample data as shown in the queries below:

CREATE DATABASE sales_database;
USE sales_database;
CREATE TABLE sales(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
product_name VARCHAR(50),
price money,
quantity INT
);
INSERT INTO sales(product_name, price, quantity)
VALUES ('iPhone Charger', $9.99, 10),
('Google Chromecast', $59.25, 5),
('Playstation DualSense Wireless Controller', $69.00, 100),
('Xbox Series S', $322.00, 3),
('Oculus QUest 2', $299.50, 7),
('Netgear Nighthawk', $236.30, 40),
('Redragon S101', $35.98, 100),
('Star Wars Action Figure', $17.50, 10),
('Mario Kart 8 Deluxe', $57.00, 5);

Once we have the sample data, we can proceed.

Create Computed Column: T-SQL

Creating a computed column in SQL Server is straightforward. Use the create table or alter table command to add a computed column.

For example, we can create a computed column that computes the total price of the items based on the price and the quantity.

Consider the example shown below:

ALTER TABLE sales
ADD total_price AS price * quantity;

The above query creates a new column based on the value of the price * quantity columns. We can query the new table as:

SELECT * FROM sales;

The resulting table is as shown:

Notice the value of the total_price column is based on the values of price and quantity columns.

Once we update the values on either column, the value in the computed column reflects the changes as shown:

UPDATE sales SET quantity = 200
WHERE id = 3;

The query above updates the quantity of the row number 3 to 200.

If we query the data again, we should see the total_price updated to reflect the changes as:

SELECT * FROM sales;

To create a computed during table creation, we can specify the column using the AS keyword followed by the formula to use to calculate the resulting set.

DROP TABLE sales;
-- recreate table and re-insert sample data with new computed column
CREATE TABLE sales(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
product_name VARCHAR(50),
price money,
quantity INT,
total_price AS price * quantity
);

This will recreate the table with the new computed column.

Create Computed Column: SSMS

You can also create a computed column using the SQL Server Management Studio. In object explorer, right expand your target database -> Tables -> Columns -> New column.

Enter the column name and the accepted data type.

Expand the column properties tab and select computed column specifications. Set the formula for the column as shown:

To apply the changes, select the main tool bar and click on the save icon:

Persisted Computed Columns

If you want to physically store the values of a computed column, you can add the persisted property during column creation.

For example, the query below adds a persisted computed column to calculate the tax of the products.

ALTER TABLE sales ADD tax AS (1.6 * quantity * price) persisted;

The resulting table is as shown:

Note, you cannot use the value of another computed column in a computed column. For example, if you try to set the value of the tax column as 1.6 * total_price, SQL Server will return an error.

Show Computed Columns: T-SQL

To show computed columns within a specific database, use the query as shown below:

SELECT * FROM sys.computed_columns;

The query should return the details of the computed columns within the selected database.

Conclusion

In this article, you understood how to create and use computed columns in SQL Server. Check the documentation to learn more.