SA0036 : DELETE statement without row limiting conditions |
![]() |
Improperly restricted DELETE statements in T-SQL can result in unintended removal of all data from a table.

Unrestricted DELETE statements in T-SQL and SQL Server databases can pose significant risks. Without a WHERE or JOIN clause, a DELETE operation will remove all rows in the specified table. This not only leads to potential data loss but can also impact database performance and integrity, especially in production environments.
For example:
1-- Example of a risky DELETE statement 2DELETE FROM Employees;
This statement deletes all records from the Employees table, which is often unintended and irreversible without a backup. Best practices dictate using a WHERE clause to limit the scope of deletion to only the necessary records, enhancing data safety and preserving critical data.
-
Unintended complete data loss from the targeted table.
-
Potential disruption to application functionality and user access.
-
Data recovery challenges requiring time-consumptive restoration processes.

Review your code to ensure DELETE statements are properly restricted to prevent accidental data loss.
Follow these steps to address the issue:
-
Examine all DELETE statements in your script to verify if they lack WHERE or JOIN clauses. Identify those that may unintentionally affect all rows.
-
For each unrestricted DELETE, determine the intended subset of rows that should be removed. Define appropriate conditions for deletion using a WHERE clause.
-
If applicable, use JOIN clauses to specify related tables that should dictate which rows to delete.
For example:
1-- Example of a corrected DELETE statement 2DELETE FROM Employees WHERE DepartmentID = 3;

The rule has a Batch scope and is applied only on the SQL script.

Name | Description | Default Value |
---|---|---|
IgnoreTempTargetTables |
Ignore targets that are temporary tables or table variables. |
yes |
ConsiderJoinOnClausesAsFilter |
The parameter specifies if the existence of JOIN clauses to be considered as row filtering criteria. |
no |
IgnoreFiltredCteTargetTables |
Ignore target tables which are common table expressions and have filtering clause. |
yes |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs

There is no additional info for this rule.

SQL
1DECLARE @temp TABLE( Id int, Name nvarchar(100)) 2 3-- Table variables are ignored by the rule. 4DELETE @temp 5 6-- All the records in the table dbo.ProductsImport will be deleted. 7-- This statement will cause analysis rule violation. 8DELETE FROM dbo.ProductsImport 9 10-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter). 11DELETE FROM #TmpRelationships |

Message | Line | Column | |
---|---|---|---|
1 | SA0036 : DELETE statement without row limiting conditions. | 8 | 0 |
