Alex Rivera | Logout

SQL Server ALTER field NOT NULL takes forever

Asked 2009-07-20T13:42:36.580
16

I want to alter a field from a table which has about 4 million records. I ensured that all of these fields values are NOT NULL and want to ALTER this field to NOT NULL

ALTER TABLE dbo.MyTable
ALTER COLUMN myColumn int NOT NULL

... seems to take forever to do this update. Any ways to speed it up or am I stuck just doing it overnight during off-hours?

Also could this cause a table lock?

sql sql-server nullable blocking ddl
Edit
Report

1 Answer

4

The only way to do this "quickly" (*) that I know of is by

  • creating a 'shadow' table which has the required layout
  • adding a trigger to the source-table so any insert/update/delete operations are copied to the shadow-table (mind to catch any NULL's that might popup!)
  • copy all the data from the source to the shadow-table, potentially in smallish chunks (make sure you can handle the already copied data by the trigger(s), make sure the data will fit in the new structure (ISNULL(?) !)
  • script out all dependencies from / to other tables
  • when all is done, do the following inside an explicit transaction :
    • get an exclusive table lock on the source-table and one on the shadowtable
    • run the scripts to drop dependencies to the source-table
    • rename the source-table to something else (eg suffix _old)
    • rename the shadow table to the source-table's original name
    • run the scripts to create all the dependencies again

You might want to do the last step outside of the transaction as it might take quite a bit of time depending on the amount and size of tables referencing this table, the first steps won't take much time at all

As always, it's probably best to do a test run on a test-server first =)

PS: please do not be tempted to recreate the FK's with NOCHECK, it renders them futile as the optimizer will not trust them nor consider them when building a query plan.

(*: where quickly comes down to : with the least possible downtime)

answered 2011-05-23T13:14:49.937

Your Answer