|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, October 29, 2012 4:10 AM
Points: 34,
Visits: 262
|
|
Hi, Can we change the formula of an exisiting computed column in a table using T-SQL? I know it can be done using SQL Server Mgmt Studio UI but I want to do it through query.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 11,645,
Visits: 27,746
|
|
i believe the calculated column has to be dropped and recreated as a new calculated column. i just tried this as a proof of concept, and I get a syntax error on the ALTER command:
create table Example(exampleID int identity(1,1) not null primary key, exampleInt int, exampletext varchar(30), myCalculatedColumn as exampleInt * 2 ) --syntax error, so this format is incorrect. alter table Example Alter Column myCalculatedColumn as (exampleInt * 2 + 10)
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 1,636,
Visits: 10,852
|
|
Yes, you have to drop old column and add a new one.
ALTER TABLE dbo.MyTable DROP COLUMN OldComputedColumn
ALTER TABLE dbo.MyTable ADD NewComputedColumn AS OtherColumn + 10 In case you weren't yet aware, you can get SSMS to write queries for you. Use the UI to set up the change you want to make, but before you click the Save button (or close the Design window), right click on the design surface and select "Generate Change Script". It will give you a script that you can cut and paste into a query window. It doesn't always give you the most efficient script or even the most efficient method for performing the task, but it will get you started. And it's a good way to get more familiar with (or refresh your memory of) DDL syntax.
Rob Schripsema Accelitec, Inc
|
|
|
|