I have 30 million rows inside a sql server database where I've added a simple tagging column. One character allowing null. I did add an index for the column.
I use it to keep track of which rows have been processed for a data extraction. During the initial development I will be restarting the extraction process from scratch (several times) and need to reset the value to '1'.
Being a neophyte database developer (who am I kidding...), my first attempt used a simple update script to reset the value of '2' (the I've processed this row) to a '1'. The first time I ran the script it executed in about 5 minutes. The last time I executed - it took 45 minutes. There has to be a better way.
Is it possible for me to speed this up by completely removing the column and recreating it with a default value of '1'? Will the speed stay consistent?
-- UPDATE ---
The next time I need to do this I'm going to try the following:
ALTER TABLE acmewidgets DROP COLUMN LegacyExtract; ALTER TABLE acmewidgets ADD LegacyExtract CHAR NOT NULL DEFAULT (1);