Update a column in a Large Table in SQL Server

Consider a table called TestTable which has more than 6 millions rows. Suppose you want to update a column with the value 100 if that column contains negative value. Let us also assume that there are over 1 million row in that column that has a negative value.

The simple way to do this is :

UPDATE TestTable SET Column = 100 WHERE Column < 0

This query is fine but it will take lot of time to finish and also it will lock the table while update running.

We can improve its performance if we run update in small packet (some rows at a time).

SET RowCount 10000

UPDATE TestTable SET Column = 100 WHERE Column < 0

WHILE @@RowCount > 0

BEGIN

SET RowCount 10000

UPDATE TestTable SET Column = 100 WHERE Column < 0

END

SET RowCount 0

The above code will update 10000 rows at a time and it will continue till @@RowCount > 0. Table is also not locked for a long time.