How can I alter this computed column in SQL Server 2008?

Paying heed to data types and constraints

Data types and constraints are critical when redefining a computed column. If your new expression involves data conversions, use CAST or CONVERT to circumvent possible crashes. Also, remember to bear in mind the impact on performance when a computed column is persisted or indexed.

Introducing conditions with CASE statement

Your computed column may need to evaluate conditions, for which, CASE is handy:

ALTER TABLE TableName
ADD ComputedCol AS (CASE WHEN Condition THEN Result1 ELSE Result2 END);


It's like a mini "Choose Your Own Adventure" in each row!

Order of columns and safeguarding data

Dropping then adding a computed column leads to it being placed at the end. If you're picky about column order, you might need to recreate the table. But don't fret - the rest of the data is left untouched. It's like a really specific, polite poltergeist!

Running queries one at a time

When tampering with your precious columns, it's best to tread lightly. Running queries sequentially gives you a better handle on any error that might crop up:

--I solemnly swear that I am up to no good
ALTER TABLE TableName
DROP COLUMN ComputedCol;

--Check if the coast is clear
ALTER TABLE TableName
ADD ComputedCol AS (NewExpression);

--Mischief Managed!


It's a safety harness while you're swinging from DROP to ADD.

Benefiting from visual tools

For those more artistically inclined, SQL Server Management Studio offers a diagram feature. It lets you visualize the alterations on your table structure, like a modern day Picasso, but for databases.