SQL Server Coding Standards: Best practices for DELETE Query

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Use the same indentation rules for DELETE statement as specified in the article of the SELECT statement.

While writing DELETE queries, make sure that the correct set of rows will be deleted by including WHERE clause. DELETE operation once committed cannot be undone.

Writing and executing a SELECT query with the similar WHERE criteria as in DELETE statement on the table should be exercised before implementing actual DELETE queries. In this way, it can be confirmed that the correct set of rows will be deleted.

When it is required to return deleted records t other calling application, consider using OUTPUT clause in the DELETE statement rather than using a temporary table or table variable to store deleted records followed by a SELECT statement.

1 2 3 4 5 6 7 8

DELETE FROM [dbo].[tblCustomers] OUTPUT deleted.FirstName ,deleted.LastName ,deleted.BirthDate ,deleted.EmailAddress ,deleted.Gender WHERE CustomerID='CUST006'