Post reply

Changing formula for a computed column

  • September 29, 2010 at 3:55 am

    #227097

    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.

  • September 29, 2010 at 5:34 am

    #1228905

    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

    --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!

  • September 29, 2010 at 9:52 am

    #1229170

    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
    Propack, Inc.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply