* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ And in this case, it drops the computed column and table as well. 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. 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. However, once you press generate script button it will pop up save change script window to show you the changes as shown below. 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. Now, it seems that we just need to press the save button and SSMS will alter the computed column without dropping it. Now you need to go to the column properties (that will be available at the bottom right) and edit the formula as shown below. Once you select modify, SSMS will open the table in the design view. In this article, I will show you how it works, step by step.īrowse 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. METHOD 2 : USING SQL SERVER MANAGEMENT STUDIOĪs 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. Once you drop and recreate the computed column, lets browse and check it again. Given below is the script.ĪDD InventoryValue AS QtyAvailable * UnitPrice 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. Lets correct it using given below methods. SELECT ProductID, QtyAvailable, UnitPrice, InventoryValueĪs you can see, I deliberately made a mistake in the formula of computed column. INSERT INTO dbo.Products (QtyAvailable, UnitPrice) , InventoryValue AS QtyAvailable -* UnitPrice Before I embark on the solution, I would like to create a sample to demonstrate the solution. In addition to this, I will discuss, why you do not need to drop computed column if you alter it through SSMS.
Given below are the two methods to alter the computer column. 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.ĪLTER COLUMN InventoryValue As (QtyAvailable * UnitPrice)įrankly speaking there is no way you can alter any computed column without dropping it.
Sql rename column how to#
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.
0 kommentar(er)
