Hi,
I have a table with 4 columns let us say A,B,C,D.
column D is computed column with formula A + '-' + B
Now, i want to add one more condition to the formula which looks like "A + '-' + B + '-' + C".

Please let me know how to do this using T-SQL as i cannot open the table in design mode in production server.

Thanks in Advance!!

First remove the current computed column.

ALTER TABLE MyTestTable
DROP COLUMN MycomputedColumn

Then add the new computed column (with same name.)

ALTER TABLE MyTestTable
ADD MyComputedColumn AS ( A + '-' + B + '-' + C )

Hi,
Thanks for the repsonse!!
Instead of dropping and recreating the column, can we modify the formula using T-SQL?
Recreating column affects the column order .

Thanks

Unfortunately, in order to change a computed column, you must DROP and re-CREATE the column.

Actual Table Column order is inconsequential.

All Best Practice documents highly encourage, and experienced DBAs will always list the columns wanted, in the presentation order desired, in the Query. Applications and users 'should' not rely upon a specific column order unless specifically requested in the query.

You 'should' never (or at least, rarely) use [ SELECT * ]

The ANSI SQL standards require that column order be enforced only with an [ ORDER BY ] clause.

Arnie,
Thanks for the reply!!
I agree with you that we should use column names in the query instead of select *.

but i think, alter column should be available to modify the formula in computed column specifcation atleast in the next version.
:-)

Thanks

Thanks Bob,

You 'might' wish to 'weigh in' and vote on this product enhancement request.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124781

(Click on the rating star that you deem appropriate.)

ALTER TABLE MyTestTable
   ADD MyComputedColumn AS ( A + '-' + B + '-' + C )

This query is not working on Ms-Access.

Can I have query for that.

ALTER TABLE MyTestTable
   ADD MyComputedColumn AS ( A + '-' + B + '-' + C )

This query is not working on Ms-Access.

Can I have query for that.

Definitely, it wont work in MS Access. You may put your question in Access forums.

http://www.accessforums.net/

And some additional suggestions:

• Don't post your own follow up question to an old and answered thead.  Many people will simply ignore those threads because they are marked as "answered".
• Choose the most appropriate forum for your question since that affects who will see it.
• Writing that something "doesn't work" is not helpful - EVER.  No one can see what you are doing or how you are doing it.  No one can see your screen and the error / warning messages that are displayed on it. Help your readers help you by posting your question with sufficient information to understand what you are doing and what you are trying to accomplish. 

is there any other way to change formula of a computed column? i tried dropping the column but i took an error because the column is related and it has data in it. 

i can create but i can't change :(

thank you

Please create a new thread for your question. This one is old and answered.