Live,
There are some workarounds for this problem:
1 - The hammer (Not tested)
You can temporarily change the name of the table, create a view with the name that the table had previously with WITH (NOLOCK), and update to the table. After the UPDATE is executed, you delete the VIEW and rename the table to the original name.
2 - UPDATES in installments
Instead of doing UPDATE to the whole table at once, you can / should make smaller updates.
UPDATE TOP(1000) tabela SET enviado = 1 WHERE enviado is null
WHILE @@rowcount > 0
BEGIN
UPDATE TOP(1000) tabela SET enviado = 1 WHERE enviado is null;
END
What this will do is run UPDATE until no record has been changed.
3 - Create a new table and copy the data
You can also create a new table ( table_v2 ) with the new structure you want to add (a column with default value for example) and copy the old contents of the
table to the table -v2 to the last known Id. The trick here is to add triggers in the table table to synchronize the information while the data is being copied. In the end, the process is similar to the first point, rename the table to table_old and rename the table_v2 to table >. and will have your data updated and the new structure without any downtime.
4 - SQL Parallel Boost ( link )
You can always have a look at this site.
I hope it was helpful.
