|
|
Error Message
Server: Msg 271, Level 16, State 1, Line 2
The column <Column Name> cannot be modified because
it is either a computed column or is the result of a
UNION operator.
Server: Msg 271, Level 16, State 1, Line 2
Column <Column Name> cannot be modified because it
is a computed column.
|
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.
If a computed column is used as a target of an INSERT or UPDATE statement, this error message will be encountered. To illustrate, here’s a script that will show how this error can be generated:
CREATE TABLE [dbo].[MovieStar] (
[MovieStarID] INT NOT NULL IDENTITY(1, 1),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[FullName] AS [FirstName] + ' ' + [LastName]
)
GO
INSERT INTO [dbo].[MovieStar] ( [FirstName], [LastName] )
VALUES ( 'Angelina', 'Jolie' ),
( 'Brad', 'Pitt'),
( 'Jennifer', 'Aniston')
GO
SELECT * FROM [dbo].[MovieStar]
GO
MovieStarID FirstName LastName FullName
------------ ---------- --------- --------------------
1 Angelina Jolie Angelina Jolie
2 Brad Pitt Brad Pitt
3 Jennifer Aniston Jennifer Aniston
UPDATE [dbo].[MovieStar]
SET [FullName] = 'Angelina "Brangelina" Jolie'
WHERE [MovieStarID] = 1
-- SQL Server 2000
Msg 271, Level 16, State 1, Line 1
Column 'FullName' cannot be modified because it is a computed column.
-- SQL Server 2005/SQL Server 2008/SQL Server 2012
Msg 271, Level 16, State 1, Line 1
The column "FullName" cannot be modified because it is either a computed column
or is the result of a UNION operator.
INSERT INTO [dbo].[MovieStar] ( [FirstName], [LastName], [FullName] )
VALUES ( 'Daniel', 'Craig', 'Daniel Craig' )
GO
-- SQL Server 2000
Msg 271, Level 16, State 1, Line 1
Column 'FullName' cannot be modified because it is a computed column.
-- SQL Server 2005/SQL Server 2008/ SQL Server 2012
Msg 271, Level 16, State 1, Line 1
The column "FullName" cannot be modified because it is either a computed column
or is the result of a UNION operator.
Solution / Work Around:
To avoid encountering this error message in an INSERT statement, simply don’t include the computed column in the list of columns being populated:
INSERT INTO [dbo].[MovieStar] ( [FirstName], [LastName] )
VALUES ( 'Daniel', 'Craig')
Similarly in the case of an UPDATE statement, instead of updating the computed column directly, update the column that is part of the expression used by the computed column.
UPDATE [dbo].[MovieStar]
SET [FirstName] = 'Angelina "Brangelina"'
WHERE [MovieStarID] = 1
Alternatively, if the value in the computed column needs to be different from the values of the columns referenced by the computed column, then one other option is to convert the computed column into an actual column.
Converting a computed column into an actual column is not just a matter of doing an ALTER TABLE ALTER COLUMN on the column as an error will be generated:
ALTER TABLE [dbo].[MovieStar] ALTER COLUMN [FullName] VARCHAR(100) NOT NULL
Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'FullName' because it is 'COMPUTED'.
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].[MovieStar] DROP COLUMN [FullName]
GO
ALTER TABLE [dbo].[MovieStar] ADD [FullName] VARCHAR(100)
GO
UPDATE [dbo].[MovieStar]
SET [FullName] = [FirstName] + ' ' + [LastName]
GO
SELECT * FROM [dbo].[MovieStar]
MovieStarID FirstName LastName FullName
------------ ---------- --------- -------------------
1 Angelina Jolie Angelina Jolie
2 Brad Pitt Brad Pitt
3 Jennifer Aniston Jennifer Aniston
4 Daniel Craig Daniel Craig
UPDATE [dbo].[MovieStar]
SET [FullName] = 'Angelina "Brangelina" Jolie'
WHERE [MovieStarID] = 1
|