Adding Column with CHECKSUM Options

  • Hello,

    We need to add a column as a checksum of. In our preprod Azure SQL environment we executed the usual

    ALTER TABLE myTable

    ADD myColumn AS CHECKSUM(myVarcharColumn) PERSISTED.

    That took us 6 hours due to the size of the table. What is the best way to execute this script in Prod, please?

  • likely the standard method of inserting into a new table with the column being calculated at that stage, and then one of 2 options

    1 - truncate source table (remove any FK before it), add new column, switch new table to original table (add fk's back if applicable) (and yes switch table can be used even if table isn't partitioned)

    2 - (method used by SSMS UI) - drop original table, rename new table to be old table - add back required FK's if applicable.

  • frederico_fonseca wrote:

    likely the standard method of inserting into a new table with the column being calculated at that stage, and then one of 2 options

    1 - truncate source table (remove any FK before it), add new column, switch new table to original table (add fk's back if applicable) (and yes switch table can be used even if table isn't partitioned)

    Can you elaborate on the reply - do you suggest to drop (truncate) source table with millions and millions of records in it?

    • This reply was modified 1 month, 1 week ago by  BOR15K.
  • BOR15K wrote:

    frederico_fonseca wrote:

    likely the standard method of inserting into a new table with the column being calculated at that stage, and then one of 2 options

    1 - truncate source table (remove any FK before it), add new column, switch new table to original table (add fk's back if applicable) (and yes switch table can be used even if table isn't partitioned)

    Can you elaborate on the reply - do you suggest to drop (truncate) source table with millions and millions of records in it?

    you are missing the first step - copy the data onto a NEW table with the NEW column.

    e.g. something like this

    -- step 1 - create temp table to hold the data 

    drop table if exists brand_new_temp_table
    select *, checksumcolumn
    into brand_new_temp_table
    from originaltable

    -- step 2 - add required indexes/FK's to brand_new_temp_table

    -- step 3
    - drop originaltable - may require removing FK's first
    - recreate table with new column
    or
    - truncate originaltable - may require removing FK's first
    - add new checksum column

    -- step 4 - switch brand_new_temp_table to originaltable

    SSMS GUI does it differently - may or not be faster.
  • Understood - this is live data and users perform CRUD commands 24/7.

  • then you have a problem as this is never going to be a slow operation - a amount of downtime will always be required, how much is going to depend (as usual).

    will this underlying table be subject to changes on a wide range of rows or only on the most recent ones - and if only on recent ones is there a way to identify what has been changed since a point in time - e.g. could you identify any new/deleted/updated rows since prior day or even prior hour?

    and if you can identify the above, what percentage of rows would be affected over total row numbers on table (would help to know total row count as it stands)

Viewing 6 posts - 1 through 5 (of 5 total)

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