SQL Server Error Messages - Msg 4928

Error Message

Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column <Column Name> because it is COMPUTED.

Causes

A computed column is computed from an expression that can use other columns in the same table. The expression can be a non-computed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a sub-query.

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query.

Computed columns can be used in SELECT lists, WHERE clauses, ORDER BY clauses or any other locations in which regular expressions can be used, with the following exceptions:

Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns. A computed column cannot be the target of an INSERT or UPDATE statement. This error message is usually encountered when a computed column definition is updated using the ALTER TABLE ALTER COLUMN. To illustrate, given the following table structure:

CREATE TABLE [dbo].[User] (
    [UserID]     INT NOT NULL IDENTITY(1, 1),
    [FirstName]  VARCHAR(50),
    [LastName]   VARCHAR(50),
    [FullName] AS [FirstName] + ' ' + [LastName]
)
GO

If the [FullName] column is to be converted to an actual column instead of being a computed column, issuing the following ALTER TABLE ALTER COLUMN statement will generate the error:

ALTER TABLE [dbo].[User] ALTER COLUMN [FullName] VARCHAR(100) NOT NULL

Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'FullName' because it is 'COMPUTED'.

Solution / Work Around

Converting a computed column into an actual physical column within a table involves multiple steps. To convert a computed column into an actual column, the computed column needs to be dropped first, a new column with the same name added, then the new column is updated with the original values using the original expression of the computed column. These steps are illustrated in the following script:

ALTER TABLE [dbo].[User] DROP COLUMN [FullName]
GO

ALTER TABLE [dbo].[User] ADD [FullName] VARCHAR(100)
GO

UPDATE [dbo].[User]
SET [FullName] = [FirstName] + ' ' + [LastName]
GO

Alternatively, instead of dropping the computed column first, the new column can be initially added assigning it a temporary name, update the newly added column with the value of the computed column, drop the computed column and lastly, rename the new column to the original name of the computed column.

ALTER TABLE [dbo].[User] ADD [FullName2] VARCHAR(100)
GO

UPDATE [dbo].[User]
SET [FullName2] = [FullName]
GO

ALTER TABLE [dbo].[User] DROP COLUMN [FullName]
GO

EXECUTE [dbo].[sp_rename] 'User.FullName2', 'FullName', 'COLUMN'