SA0272 : SELECT statement without row limiting conditions

The topic describes the SA0272 analysis rule.

Message

SELECT statement without row limiting conditions

Description

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

Consider reviewing your code to avoid returning more rows than necessary.

How to fix

Add WHERE or JOIN filtering clause to the SELECT statement or add rule suppression comment.

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.

yes
IgnoreFiltredCteTargetTables

The parameter specifies whether to ignore table sources that are CTE-s and have query definition that has filtering clauses.

yes
IgnoreNonFiltredCteTableSources

The parameter specifies whether to ignore table sources that are CTE-s and have query definition that has no filtering clauses.

no
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Performance Rules, New Rules

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.
 5SELECT     @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.
10SELECT * FROM dbo.ProductsImport
11
12-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter).
13SELECT * FROM #TmpRelationships
14
15-- Temporary tables are ignored by the rule ( configurable by the IgnoreTempTargetTables rule parameter).
16SELECT *
17FROM #TmpRelationships TT
18
19
20-- This SELECT statement will be ignored by the rule as it has a filtering condition.
21SELECT *
22FROM       TestTable
23INNER JOIN TestTable2
24ON         TestTable.TestTable2Id=TestTable2.Id
25
26SELECT TT.*
27FROM TmpRelationships TT /*IGNORE:SA0272*/
28
29DECLARE @VariableCheck TABLE (COL1 INT NOT NULL)
30
31SELECT 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 ) 
43SELECT * 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 ) 
54SELECT * FROM SQLTEMP
55
56;WITH 
57SQLTEMP1 AS( SELECT mp_program FROM mp_latest_rev_tmp )
58SELECT * FROM SQLTEMP1 а
Analysis Results
  Message Line Column
1 SA0272 : SELECT statement without row limiting conditions. 10 0
2 SA0272 : SELECT statement without row limiting conditions. 43 0
3 SA0272 : SELECT statement without row limiting conditions. 42 12
4 SA0272 : SELECT statement without row limiting conditions. 54 0
5 SA0272 : SELECT statement without row limiting conditions. 53 12
6 SA0272 : SELECT statement without row limiting conditions. 58 0
7 SA0272 : SELECT statement without row limiting conditions. 57 13
See Also

Other Resources

Analysis Rules