Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing formula for a computed column Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 3:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 10:56 PM
Points: 35, Visits: 277
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.
Post #995032
Posted Wednesday, September 29, 2010 5:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 12,910, Visits: 32,020
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
Post #995097
Posted Wednesday, September 29, 2010 9:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:24 AM
Points: 1,974, Visits: 10,962
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
Post #995387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse