updating millions of rows in multiple tables sql server 2005 I have a situation where I am going to need to update millions of rows in 11 different tables. I only have to update 1 column in each of the tables but I was wondering what would be the most efficient way to do this? One thought was to drop the indexes on the tables temporarily and run update statements in batches of 100,000 on 1 table at a time. We are planning to take the database offline to prevent potential locking issues. I would like to know what the fastest way to update all of these tables would be. Our database is Microsoft SQL Server 2005. Below are the counts of rows from the 11 tables that need updated.

Table 1 206,108

Table 2 560,859

Table 3 690,946

Table 4 1,106,173

Table 5 3,159,963

Table 6 3,250,619

Table 7 4,046,048

Table 8 5,638,351

Table 9 8,880,814

Table 10 18,060,095

Table 11 76,294,745
Most efficient is a fuzzy term. You're talking about updating about 122 million rows. If you start running naive updates now, it will probably finish before you can create a useful estimate. (I'd expect it to take about half an hour on my development machine. Updating 50 million rows takes 12 minutes.)

Dropping indexes beforehand is fine if you have your DDL under version control, and you can rebuild the indexes more or less automatically. Dropping all constraints should make the updates run faster still, but again I'd do that only if 


execution time were critical, 
DDL were under version control, and 
I could rebuild all the constraints more or less automatically.


Cascading foreign key constraints can be an issue. 

Personally, I try to avoid situations where execution time is critical. I don't usually drop primary key and foreign key constraints. And I always rehearse outside the maintenance window.