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
