[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
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.