Free bangkok chat sex cam - Sql updating one table from another

by  |  13-Aug-2015 12:23

Many a times, you come across a requirement to update a large table in SQL Server that has millions of rows (say more than 5 millions) in it.In this article I will demonstrate a fast way to update rows in a large table Consider a table called which has more than 5 millions rows.Suppose you want to update a column with the value 0, if it that column contains negative value.

The usual way to write the update method is as shown below: The issue with this query is that it will take a lot of time as it affects 2 million rows and also locks the table during the update.

You can improve the performance of an update operation by updating the table in smaller groups.

Consider the following code: The above code updates 10000 rows at a time and the loop continues till @@rowcount has a value greater than zero. Best practices while updating large tables in SQL Server1.

Always use a WHERE clause to limit the data that is to be updated2.

If the table has too many indices, it is better to disable them during update and enable it again after update3.

Community Discussion