SQL Server Portal

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.

Sample :

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

As you can see, I deliberately made a mistake in the formula of computed column. Lets correct it using given below methods.

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

Conclusion :
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.

Read Full Post »