HOW TO UPDATE LARGE TABLES IN FASTEST WAY....... IN SQL SERVER

FASTEST WAY TO UPDATE ROWS IN A LARGE TABLES IN SQL SERVER

The key concept here will be to improve the performance of an update operation by updating the tables in smallest manner.

For E.G:

consider a table called test with contains more than 5 millions records rows. Let us also assume what we need to update,,,say there are over 3  millions rows in that column that has a negative number so we need to update all the rows (i.e upto 3 millions) containing the negative numbers to positive numbers .

NOW normal query will be(not recommended):

update test set columnname=0 where columnanme<0;

THAT IS ROWCOUNT TO RESCUE>>>>>>>>>>>>>>>>>>>

set rowcount 10000

update test set columnname=0 where columnanme<0;

while @@rowcount>0

BEGIN

set rowcount 10000

update test set columnname=0 where columnanme<0;

//

I.E

it will updates 10000 row at a time and then loop continues till @@rowcount>0.

this will ensure that table will not locked and updation is done smoothly in less time.