Batch Delete – “One spoon at a time” the smart DELETE

BATCH DELETE

Delete in SQL server is not as simple as it looks.

Yes it has simple line of statement.

delete BatchDeleteDemo_Tab where name='Aadarshini'

Then what is the issue, there are several problem like

• transaction log
• disk space
• time consuming
• locking

It depends how many rows are affected by the DELETE statement above, but generally, it is almost never a good idea to run a ‘straight forward’ deletes.

SQL Server is working in a transactional manner, the rows which are to be deleted have to be logged in the transaction log, and if there is not enough disk space for the log, then we have a real problem – the log is full, no other transactions can be run… you get the idea.

When you delete rows from a table, the rollback transaction information is always written fully to the log file, and it will grow until the transaction is committed or rolled back.

So what is the option left for us:

while (1=1)

begin

      delete top 3 BatchDeleteDemo_Tab where name='Aadarshini'

      If @@rowcount = 0

            BREAK

      ELSE

            Continue

End

The while loop will delete 3 record at a time and then check if there are more rows to delete, if there are not then the loop gets terminated

CONCLUSION: This method is better and more efficient for obvious reasons: the log does not grow by too much and the log space is reused; it is easy to terminate the delete quickly and only the transaction of the last batch will be reverted; the lock escalations do not have a huge impact on the entire system since it is for few rows at a time.  

Post Reference: Vikram Aristocratic Elfin Share

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

Different ways to find TOP N records.

For our explanation we have taken a table ‘parent_tab ‘ having the following seven records data.

select * from parent_tab

first_id    name

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

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Various ways to achieve this : Our objective is to find first 4 records from the above table

1. Using ROWCOUNT

set rowcount 4

select name from parent_tab

set rowcount 0

name

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

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

2. Using ROW_NUMBER

select name from

(

select row_number() over (order by first_id) as rno, name from parent_tab

) as tab where rno <= 4

name

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

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

3. Using Top N

select top 4 name from parent_tab

name

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

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

If you know any other way to achieve this, please light up your suggestion to me.

Post Reference: Vikram Aristocratic Elfin Share