Tuesday, November 27, 2018

Can I change a normal SQL Server column to a computed column?

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