• I worked with Join previously and found that It takes around 15-20 min to delete 1 million records from 20 tables.

    Then I changed the strategy and first pick and insert the ids (primary key) in DeletionIds table and then delete all foreign keys data (19 tables) first and then remove the data from main table and deletion time reduced to 10-13 minutes.

    Why TOP (100) PERCENT?

    I just do some R & D on web and try to find quick way to delete data from database. With this I found a 20-30% improvement in deletion process as compared to DELETE FROM Table1.

    Mass Deletion?

    1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)

    2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.

    @MysteryJimbo; I will look into "sliding window partitioned table setup". Does this support all SQL 2008 R2 versions? I am using Standard version of SQL Server.

    Thanks