Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Changing formula for a computed column Expand / Collapse
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: Thursday, December 10, 2015 12:06 AM
Points: 38, Visits: 284
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



Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 14,549, Visits: 38,413
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)


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #995097
Posted Wednesday, September 29, 2010 9:52 AM



Group: General Forum Members
Last Login: Thursday, July 14, 2016 10:57 AM
Points: 2,263, Visits: 11,042
Yes, you have to drop old column and add a new one.

DROP COLUMN OldComputedColumn

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
Propack, Inc.
Post #995387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse