Deleting records from sql table efficiently

This blog is dedicated to let the sql community know that there is a better way of how we can delete the records from a table.
Delete keyword in SQL is a DML(Data Manipulation Language) statement and whenever delete statement is run, the sql engine has to get a lock on the table before it can delete the records.
If the table has millions of rows, then it will take a long time for delete to happen as well. Moreover, the delete also blocks the other DML statements to occur before it can complete it's job. Example: Other inserts , updates will be blocked by this delete because it has the table lock. This situation can lead to blocking on the database server and also increase in I/O , memory usage etc. To avoid to this situation and to mitigate the effect  , i am going to present a way in which we will store the ID's from the table to be deleted in to a table variable and then use the table  variable ID's to  delete the records from the original table. Using this solution, we are not locking the table for the time in which the data needs  to be fetched and also using the ID column which should have an index on it, making the access to data faster.


The following is the T SQL code of how you can do this:


DECLARE @IdsToBeDeleted TABLE (Id INT PRIMARY KEY);



INSERT INTO @IdsToBeDeleted 
  (
    Id
  )
SELECT Id
FROM   Table(These are the ID's that need to be deleted)
--No Locking until here



BEGIN TRANSACTION;


DELETE s
FROM   Table  s 
       JOIN @ IdsToBeDeleted  si
            ON  si.Id = s.Id


 COMMIT TRANSACTION;


This way you can make your delete's run efficiently and will have lower load on the server.
I am thankful that you have gone through the technique i have mentioned here. Please leave some comments and let me know what you think or if you have any questions.
Abhi