Updating rows in sql

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.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.

updating rows in sql-74updating rows in sql-73

Let’s say you have a table in which you want to delete millions of records.

If the goal was to remove all then we could simply use TRUNCATE.

However, if we want to remove records which meet some certain criteria then executing something like this will cause more trouble that it is worth.

while @rc --Keeping track of inserts declare @rc int; declare @table Rows int; declare @batch Size int; declare @start int; declare @end int; set @rc = 1; select @table Rows = count(*) from source Table; --initialize to total number of rows in table set @batch Size = 10000; set @start = 1; set @end = @start @batch Size - 1; while @rc create table #tmp ( rownbr int ,col A varchar(10) ); declare @rc int; declare @table Rows int; declare @batch Size int; set @rc = 1; select @table Rows = count(*) from source Table; --initialize to total number of rows in table set @batch Size = 10000; while @rc --Updates in batches by row numbers declare @rc int; declare @table Rows int; declare @batch Size int; declare @start int; declare @end int; set @rc = 1; select @table Rows = count(*) from Colors; set @batch Size = 10000; set @start = 1; set @end = @start @batch Size - 1; while @rc I want to clarify some things about this post. If you are not careful when batching you can actually make things worse! These are contrived examples meant to demonstrate a methodology.

I got good feedback from random internet strangers and want to make sure everyone understands this.

Leave a Reply