Click or drag to resize

SA0181 : The query joins too many table sources

The topic describes the SA0181 analysis rule.

Message

The query joins too many table sources

Description

The rule reports queries that joins more than the configured maximum number of table sources.

Scope

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

Parameters
NameDescriptionDefault Value
MaxTableSources

Maximum number of table sources, which can be joined in a query.

5

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
  1-- Filtering column wrapped inside a function is used in the WHERE clause
  2select *
  3from users
  4where substring( firstname ,
  5                 1 ,
  6                 1 ) = 'm'
  7
  8select *
  9from users
 10where isnull( status ,
 11              0 ) > 0 -- CAST is ignored
 12
 13select *
 14from users
 15where cast( status as int ) > 0
 16
 17select *
 18from users
 19where convert(  status ,
 20               111 ) > 0
 21
 22select *
 23from users
 24where coalesce( status ,
 25                2 ,
 26                0 ,
 27                1 ) > 0 -- Filtering column wrapped inside an user defined function is used in the WHERE clause
 28
 29select *
 30from users
 31where firstname like 'm%'
 32
 33select OrderId ,
 34       ProfileId ,
 35       OrderNumber ,
 36       Created
 37from Orders
 38where dbo.fnGetDate( Created ) > getdate(  ) - 1 or
 39      dateadd( day ,
 40               2 ,
 41               Created ) > getdate(  ) -- Filtering column wrapped inside a built in function is used in the WHERE clause
 42
 43select OrderId ,
 44       OrderNumber ,
 45       Created
 46from Orders
 47where fnBuiltInGetDate( Created ) /*IGNORE:SA0127*/ > '2010/09/01' or
 48      dateadd( day ,
 49               2 ,
 50               getdate(  ) ) > getdate(  )
 51
 52select *
 53from users
 54where substring( firstname ,
 55                 1 ,
 56                 1 ) = 'm' /*IGNORE:SA0127(LINE)*/
 57
 58select *
 59from Table_1 as t1
 60     join Table_2 as t2
 61                  on  ltrim( t1.testdata1 ) = substring( t2.testdata1 ,
 62                                                         1 ,
 63                                                         1 );
 64
 65delete from Table_1
 66from Table_1 as t1
 67     join Table_2 as t2
 68                  on  ltrim( t1.testdata1 ) = substring( t2.testdata1 ,
 69                                                         1 ,
 70                                                         1 ) ,
 71     Table_3 as t3 ,
 72     Table_4 as t4
 73     inner join Table_5 as t5
 74                  on  t4.t5id = t5.id ,
 75     Table_6 as t6 ,
 76     Table_7 as t7
 77
 78
 79update Table1 set col1 = 0
 80from Table_1 as t1
 81     join Table_2 as t2
 82                  on  ltrim( t1.testdata1 ) = substring( t2.testdata1 ,
 83                                                         1 ,
 84                                                         1 ) ,
 85     Table_3 as t3 ,
 86     Table_4 as t4
 87     inner join Table_5 as t5
 88                  on  t4.t5id = t5.id ,
 89     Table_6 as t6 ,
 90     Table_7 as t7
 91
 92select *
 93from Table_1 as t1
 94     join Table_2 as t2
 95                  on  ltrim( t1.testdata1 ) = substring( t2.testdata1 ,
 96                                                         1 ,
 97                                                         1 ) ,
 98     Table_3 as t3 ,
 99     Table_4 as t4
100     inner join Table_5 as t5
101                  on  t4.t5id = t5.id ,
102     Table_6 as t6 ,
103     Table_7 as t7

Analysis Results

 MessageLineColumn
1SA0181 : The query joins too many table sources.650
2SA0181 : The query joins too many table sources.790
3SA0181 : The query joins too many table sources.920
See Also

Other Resources