This article demonstrates how to use T-SQL to change an existing column to 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 WITHOUT a Computed Column

First, let’s create a table without a computed column.

CREATE TABLE Person
  (
    PersonID int IDENTITY (1,1) NOT NULL,
    FirstName varchar(70), 
    LastName varchar(70),
    FullName varchar(140)
  );

INSERT INTO Person (FirstName, LastName, FullName)
VALUES 
  ('Homer', 'Seinfeld', 'Homer Seinfeld'),
  ('Bart', 'Costanza', 'Bart Costanza'),
  ('Marge', 'Kramer', 'Marge Kramer');

SELECT *
FROM Person;

Result:

+------------+-------------+------------+----------------+
| PersonID   | FirstName   | LastName   | FullName       |
|------------+-------------+------------+----------------|
| 1          | Homer       | Seinfeld   | Homer Seinfeld |
| 2          | Bart        | Costanza   | Bart Costanza  |
| 3          | Marge       | Kramer     | Marge Kramer   |
+------------+-------------+------------+----------------+

You can see that I’m doubling up when I insert data. The full name is a combination of the first name and last name, and I’m retyping this data for every row.

This could also cause issues when updating the table. I’d need to remember to update two columns any time somebody changed their first or second name (and three columns if they changed both).

This is a good candidate for a computed column.

Example 2 – Change the Column to a Computed Column

To change a column to a computed column, you need to drop the column first, then create it with the new definition.

Here’s how to change the FullName column to a computed column.

ALTER TABLE Person DROP COLUMN FullName;

ALTER TABLE Person ADD FullName AS (CONCAT(FirstName, ' ', LastName));

We’ve just “changed” the column called FullName to a computed column. It concatenates the FirstName and LastName columns.

Here’s what happens when we select the table’s contents:

SELECT *
FROM Person;

Result:

+------------+-------------+------------+----------------+
| PersonID   | FirstName   | LastName   | FullName       |
|------------+-------------+------------+----------------|
| 1          | Homer       | Seinfeld   | Homer Seinfeld |
| 2          | Bart        | Costanza   | Bart Costanza  |
| 3          | Marge       | Kramer     | Marge Kramer   |
+------------+-------------+------------+----------------+

Example 3 – Update a Value

Now, if a value is updated in the FirstName or LastName columns, this will affect the value returned by the computed column. No need to update it in two columns.

Example:

UPDATE Person
SET LastName = 'Bourne'
WHERE PersonId = 3;

SELECT *
FROM Person;

Result:

+------------+-------------+------------+----------------+
| PersonID   | FirstName   | LastName   | FullName       |
|------------+-------------+------------+----------------|
| 1          | Homer       | Seinfeld   | Homer Seinfeld |
| 2          | Bart        | Costanza   | Bart Costanza  |
| 3          | Marge       | Bourne     | Marge Bourne   |
+------------+-------------+------------+----------------+