[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Cannot alter a computed column on a table

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
Tips
Troubleshooting
Tuning

Cannot alter a computed column on a table.


Applies to: Microsoft SQL Server 2008 or later.
 

Problem Description.
 

I created a table with a computed column as shown below:


USE Morillo

CREATE TABLE [dbo].[OrderDetails](
[Price] [numeric](8,3) NOT NULL,
[SubTotal] AS [Price] * [Quantity],
[Quantity] [numeric](8,3) NOT NULL,
[Discount] [numeric](8,3) NOT NULL)


Then I decided I want to modify (alter) the computed column to include the Discount column as shown below:


ALTER TABLE dbo.[OrderDetails]
ALTER COLUMN [SubTotal] AS ([Price] * [Quantity] * [Discount])


However, when I ran the ALTER COLUMN the following error appeared:


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.




Cause.


A computed column cannot be modified using the ALTER COLUMN argument of an ALTER TABLE statement. This is described on the "Arguments" section of this MSDN article.


Solution.


A workaround is to drop the column and recreate it.

ALTER TABLE dbo.[OrderDetails]
DROP COLUMN [SubTotal]

ALTER TABLE dbo.[OrderDetails]
ADD [SubTotal] AS ([Price] * [Quantity] * [Discount])



 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.