BATCH UPDATE IN SQL SERVER. A much relif for those who are working with Production server

BATCH UPDATE  IN SQL SERVER. A much relif for those who are working with Production server

During my deputation to Onsite, once I experienced a situation where a table from the PRODUCTION/LIVE DB needs an updation for a particular column value.

I remember a situation where where a junior SQL devloper execute update statement on that table which contain data in GBs. As a result of that update statement every thing get stopped.

Those of us, who work in the Production / Live DB, know the pain, of firing an update query on a huge table. This is very problematic as this will lead to the following

• The entire table will be locked by your application , so other users /  application will not be able to perform the DDL on this table
•  The TempDB will grow huge
•  A update lock on a table will stop other users , for making changes in the table.

So what is option to resolve this issue?

I create a solution where I am updateing a table with Active cloumn TRUE where ever it gets ‘FALSE’ value in the column.

For demostration purpose our table contain 10 record with the Active column value ‘FALSE’ and we are updating 2 record at a time. So the batch size of updation in our example is 2. U can set your own value depending upon your application useage.

select * from user_tab where active = 'False'

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           0

2           Orpita                                             0

3           Aalap                                              0

4           Trishona                                           0

5           Shobna                                             0

6           Eekalabya                                          0

7           Fani                                               0

8           Kishalaya                                          0

9           Mrinmoy                                            0

10          Satyajit                                           0

(10 row(s) affected)

And we are updating the FALSE value of Active column to TRUE. But here we are updating two record at a time.

declare @cnt int

declare @total_record int

declare @row_processed int

declare @row_count int

select @cnt = 1

select @row_processed = 0

select @total_record=count(*) from parent_tab

while ( @cnt > 0)

begin

      set rowcount 2

      update user_tab

      set active = 'True'

      where active = 'False'

      select @row_count = @@rowcount

     

      if(@row_count = 0)

      begin

            select @cnt = 0

      end        

      else

      begin

            select @row_processed = @row_processed + @row_count

            print 'The total number of rows effected :'+convert(varchar,@row_processed)

      end

end

print 'Update complete'

set rowcount 0

//OUTPUT OF SCRIPT

(2 row(s) affected)

The total number of rows effected :2

(2 row(s) affected)

The total number of rows effected :4

(2 row(s) affected)

The total number of rows effected :6

(2 row(s) affected)

The total number of rows effected :8

(2 row(s) affected)

The total number of rows effected :10

(0 row(s) affected)

Update complete

After execution of this script the result of the table is as follows

select * from user_tab where active = 'False'

user_id     user_name                                          active

----------- -------------------------------------------------- ------

(0 row(s) affected)

So we can see there is no record with ACTIVE column value ‘FALSE’. Let’s check for ACTIVE = ‘TRUE’

select * from user_tab where active = 'True'

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Fani                                               1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

(10 row(s) affected)

So all records gets updated with ACTIVE status sets to ‘TRUE’.

If you find any problem in understanding this code, revert me at viki.keshari@gmail.com