SA0036 : DELETE statement without row limiting conditions

The topic describes the SA0036 analysis rule.

Message

DELETE statement without row limiting conditions

Description

The rule looks for DELETE statements not having neither WHERE nor JOIN clauses.

How to fix

Consider reviewing your code to avoid unintentionally losing all the rows in the table.

Scope

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

Parameters
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
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @temp TABLE( Id int,
 2                     Name nvarchar(100))
 3
 4-- Table variables are ignored by the rule.
 5DELETE     @temp
 6-- Table variables are ignored by the rule.
 7
 8-- All the records in the table dbo.ProductsImport will be deleted. 
 9-- This statement will cause analysis rule violation.
10DELETE FROM dbo.ProductsImport
11
12-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter).
13DELETE FROM #TmpRelationships
14
15-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter).
16DELETE FROM TT
17FROM #TmpRelationships TT
18
19
20-- This DELETE statement will be ignored by the rule as it has a filtering condition.
21DELETE     TestTable
22FROM       TestTable
23INNER JOIN TestTable2
24ON         TestTable.TestTable2Id=TestTable2.Id
25
26DELETE FROM TT
27FROM TmpRelationships TT /*IGNORE:SA0036*/
28
29DECLARE @VariableCheck TABLE (COL1 INT NOT NULL)
30
31DELETE V 
32FROM @VariableCheck V
33
34;WITH 
35SQLTEMP1  
36        AS( SELECT mp_program 
37            FROM mp_latest_rev_tmp 
38            LEFT OUTER JOIN SQLTEMP 
39            ON  eoae_ac_reg_no  = mp_aircraft 
40            WHERE mp_guid    = 1 
41            AND  eohdr_ouinstance IS NULL),
42SQLTEMP AS( SELECT mp_program FROM mp_latest_rev_tmp ) 
43DELETE FROM SQLTEMP1
44
45;WITH 
46SQLTEMP1  
47        AS( SELECT mp_program 
48            FROM mp_latest_rev_tmp 
49            LEFT OUTER JOIN SQLTEMP 
50            ON  eoae_ac_reg_no  = mp_aircraft 
51            WHERE mp_guid    = 1 
52            AND  eohdr_ouinstance IS NULL),
53SQLTEMP AS( SELECT mp_program FROM mp_latest_rev_tmp ) 
54DELETE FROM SQLTEMP
55
56;WITH 
57SQLTEMP1 AS( SELECT mp_program FROM mp_latest_rev_tmp )
58DELETE FROM SQLTEMP1
Analysis Results
  Message Line Column
1 SA0036 : DELETE statement without row limiting conditions. 10 0
2 SA0036 : DELETE statement without row limiting conditions. 21 0
3 SA0036 : DELETE statement without row limiting conditions. 54 0
4 SA0036 : DELETE statement without row limiting conditions. 58 0
See Also

Other Resources

Analysis Rules