Nope. Per BOL, "To change an existing column to a computed column you must drop and re-create the computed column." Though because the computed column isn't there yet, I am fairly sure they meant '...drop and re-create the existing column'. See here: Not going to happen
So what do you do? Well, you could create a new table with computed columns and copy the data into it, OR you could rename your existing columns and create new ones with the intended computations. I like the 2nd option a little better because I don't have to move a bunch of data around AND because I can see my old and computed columns side by side. Then, when I'm sure the computed columns are what I need -- I drop the old columns. In this example I am adding 3 new computed columns to my CUSTOMER table. The computations are based on other columns in the CUSTOMER table, but you see what I mean. We rename the old column and define the new one w/the desired computation:
-- rename old column
EXEC sp_rename 'CUSTOMER.TSLONG', 'TSLONGold', 'COLUMN';
-- add new column
w/computation
ALTER TABLE CUSTOMER ADD TSLONG AS (CONVERT(BIGINT,TS,0));
-- rename old column
EXEC sp_rename 'CUSTOMER.LOOKUPID', 'LOOKUPIDold', 'COLUMN';
-- add new column
w/computation
ALTER TABLE CUSTOMER ADD LOOKUPID AS (CASE
LEN(CUSTOMID) WHEN (0) THEN '8-'+CONVERT(NVARCHAR(20),SEQUENCEID,0) ELSE CUSTOMID END);
-- rename old column
EXEC sp_rename 'CUSTOMER.GENDER', 'GENDERold', 'COLUMN';
-- add new column
w/computation
ALTER TABLE CUSTOMER ADD GENDER AS (CASE GENDERCODE
WHEN (0) THEN 'Unknown'
WHEN (1) THEN 'Female' END
WHEN (2) THEN 'Male'
);
Once you're sure everything is good, you'll use this to DROP the old columns:
-- drop old columns
ALTER TABLE CUSTOMER
DROP COLUMN TSLONGold;
ALTER TABLE CUSTOMER
DROP COLUMN LOOKUPIDold;
ALTER TABLE CUSTOMER
DROP COLUMN GENDERold;Or, you could even use this:
ALTER TABLE CUSTOMER
DROP COLUMN TSLONGold,LOOKUPIDold,GENDERold;
Did you know you drop more than one table column at a time? How cool is that?! Better be safe.
Hope you found this helpful.
No comments:
Post a Comment