Fastest Way to Update Rows in a Large Table in SQL Server

The usual way to write the update method is as shown below:

UPDATE test

SET col=0

WHERE col<0

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:

SET rowcount 10000

UPDATE test

SET col=0

WHERE col<0

WHILE @@rowcount>0

BEGIN

SET rowcount 10000

UPDATE test

SET col=0

WHERE col<0

END

SET rowcount 0

The above code updates 10000 rows at a time and the loop continues till @@rowcount has a value greater than zero. This ensures that the table is not locked.