How to alter an existing computed column in a table is usually a trouble making statement because in computed column case we also try to implement a normal alter statement as shown below and end up with the error message. However, it is a usual perception that you can alter any computed column with the help of SQL Server management Studio (SSMS) without dropping it.
ALTER TABLE dbo.Products ALTER COLUMN InventoryValue As (QtyAvailable * UnitPrice) --OUTPUT
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘As’.
Frankly speaking there is no way you can alter any computed column without dropping it. Given below are the two methods to alter the computer column. In addition to this, I will discuss, why you do not need to drop computed column if you alter it through SSMS. Before I embark on the solution, I would like to create a sample to demonstrate the solution.
USE tempdb GO DROP TABLE dbo.Products GO CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL , QtyAvailable smallint , UnitPrice money , InventoryValue AS QtyAvailable --* UnitPrice ); -- Insert values into the table. INSERT INTO dbo.Products (QtyAvailable, UnitPrice) VALUES (25, 2.00), (10, 1.5); -- Display the rows in the table. SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue FROM dbo.Products; --OUTPUT
METHOD 1 : USING T-SQL
In this method, we need to first drop the computed column and then add it as a new column to accommodate the altered computed column in any table. Given below is the script.
USE tempdb GO ALTER TABLE dbo.Products DROP COLUMN InventoryValue ALTER TABLE dbo.Products ADD InventoryValue AS QtyAvailable * UnitPrice
Once you drop and recreate the computed column, lets browse and check it again.
USE tempdb GO -- Display the rows in the table. SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue FROM dbo.Products; --OUTPUT
METHOD 2 : USING SQL SERVER MANAGEMENT STUDIO
As I mentioned above, it is a general perception that if you alter computed column from SSMS, you do not need to drop it, it alters computed column automatically. In this article, I will show you how it works, step by step.
Step 1 :
Browse the table and select the computed column you need to modify using SSMS and right click on it and further select modify as shown below.
Step 2 :
Once you select modify, SSMS will open the table in the design view. Now you need to go to the column properties (that will be available at the bottom right) and edit the formula as shown below.
Step 3 :
Now, it seems that we just need to press the save button and SSMS will alter the computed column without dropping it. But hang on a second, instead of pressing the save button, press the generate script button to see what changes SSMS will implement in case of pressing save button. However, once you press generate script button it will pop up save change script window to show you the changes as shown below.
Given below is the complete script I copied from the generate script window for your reference that will be implemented once you press the save button. As you can see, SSMS will create a temporary table with the correct computed column formula, then migrate all the data and drop the old table and rename the temporary table to the old table name. And in this case, it drops the computed column and table as well.
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_Products ( ProductID int NOT NULL IDENTITY (1, 1), QtyAvailable smallint NULL, UnitPrice money NULL, InventoryValue AS ([QtyAvailable]* [UnitPrice]) ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_Products SET (LOCK_ESCALATION = TABLE) GO SET IDENTITY_INSERT dbo.Tmp_Products ON GO IF EXISTS(SELECT * FROM dbo.Products) EXEC('INSERT INTO dbo.Tmp_Products (ProductID, QtyAvailable, UnitPrice) SELECT ProductID, QtyAvailable, UnitPrice FROM dbo.Products WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_Products OFF GO DROP TABLE dbo.Products GO EXECUTE sp_rename N'dbo.Tmp_Products', N'Products', 'OBJECT' GO COMMIT
As you can see in both methods, you need to drop and recreate the computed column in order to alter it. In addition to this, the perception that “if you alter the computed column via SSMS, it alters it without dropping it” was proved wrong.