SA0242 : COUNT aggregate function used instead of EXISTS

The topic describes the SA0242 analysis rule.

Message

COUNT aggregate function used instead of EXISTS

Description

The rule checks T-SQL code for usages of the COUNT aggregate function to get if any rows exists in a table.

Using the EXISTS predicate function has better performance and code readability.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1-- using COUNT
 2if((select count(*)
 3    from Table1
 4    where col1>0)!=0)
 5begin
 6    print 'There are rows with col1>0'
 7end
 8
 9select *
10from Table2 t2
11where (select count(t1.col2)
12       from Table1 t1
13       where t1.col1=t2.col1)!=0
14
15
16-- using EXISTS
17if(exists(select *
18    from Table1
19    where col1>0) )
20begin
21    print 'There are rows with col1>0'
22end
23
24select *
25from Table2 t2
26where exists (select count(t1.col2)
27       from Table1 t1
28       where t1.col1=t2.col1 and t1.col2 is not null)

Analysis Results

  Message Line Column
1 SA0242 : COUNT aggregate function used instead of EXISTS. 2 11
2 SA0242 : COUNT aggregate function used instead of EXISTS. 11 14
See Also

Other Resources