Click or drag to resize

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, New Rules

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

 MessageLineColumn
1SA0242 : COUNT aggregate function used instead of EXISTS.211
2SA0242 : COUNT aggregate function used instead of EXISTS.1114
See Also

Other Resources