Sql server - recursive delete
Those are the best and most efficient ones. For production queries I would use 2.
The only other ways I can think of would (IMO) only be suitable for quick and dirty removal of data in a test environment (avoiding the need to analyse the correct order)
- Disable all FKs delete the desired data then re-enable the FKs. This is inefficient as they need to be re-enabled
WITH CHECKto avoid leaving the FKs in an untrusted state which means that all preserved data needs to be re-validated. - List out all
DELETEstatements on affected tables in arbitrary order and run the batch as many times as necessary until it succeeds with no FK errors.
Oracle solution:
How to generate DELETE statements in PL/SQL, based on the tables FK relations?
SQL Server solution:
Generate Delete Statement From Foreign Key Relationships in SQL 2008?
Hope it helps
Cascade delete is easy, well-performing and reliable. Works over multiple levels. The query plans are interesting and seem well-optimized.
If you want manual deletes, make sure to issue just one query per table for efficiency reasons. You can use joins in a delete statement to join to the parent level(s) to filter the rows to be deleted.