Adding Column with CHECKSUM Options

Question

Adding Column with CHECKSUM Options

BOR15K

SSCertifiable

Points: 5894
More actions
August 19, 2024 at 7:32 am

#4447161

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?

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

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

frederico_fonseca SSCoach Points: 16164 More actions · Answer 1

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.

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

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 3 months, 3 weeks ago by BOR15K.

frederico_fonseca SSCoach Points: 16164 More actions · Answer 3

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.

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Understood - this is live data and users perform CRUD commands 24/7.

frederico_fonseca SSCoach Points: 16164 More actions · Answer 5

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)