Click or drag to resize

SA0041 : Avoid joining with views

The topic describes the SA0041 analysis rule.

Message

Avoid joining with views

Description

The rule checks for joining with views as this may have performance implication when used without having good knowledge of the underlying tables and may lead to unnecessary joins.

Although views are useful for many reasons, they hide the underlying sources and may mislead unacquainted developers and produce redundant joins.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1-- The SELECT statement will cause rule violation there are several views were joined in this query. 
 2
 3SELECT           e.EmployeeID
 4          , e.Title
 5          , e.FirstName
 6          , e.MiddleName
 7          , e.LastName
 8          , e.Suffix
 9          , e.JobTitle
10          , e.Phone
11          , e.EmailAddress
12          , e.EmailPromotion
13          , e.AddressLine1
14          , e.AddressLine2
15          , e.City
16          , e.StateProvinceName
17          , e.PostalCode
18          , e.CountryRegionName
19          , e.AdditionalContactInfo
20FROM      
21            HumanResources.vEmployee e
22INNER JOIN  HumanResources.vEmployeeDepartment ed
23ON          ed.EmployeeID=e.EmployeeID,
24                        HumanResources.vEmployeeDepartmentHistory edh,
25                        Employee a
26
27--  Rows are selected form the HumanResources.vEmployee view only.
28SELECT                 e.EmployeeID
29          , e.Title
30          , e.FirstName
31          , e.MiddleName
32          , e.LastName
33          , e.Suffix
34          , e.JobTitle
35          , e.Phone
36          , e.EmailAddress
37          , e.EmailPromotion
38          , e.AddressLine1
39          , e.AddressLine2
40          , e.City
41          , e.StateProvinceName
42          , e.PostalCode
43          , e.CountryRegionName
44          , e.AdditionalContactInfo
45FROM      
46            HumanResources.vEmployee e

Analysis Results

 MessageLineColumn
1SA0041 : Avoid joining with views.2127
2SA0041 : Avoid joining with views.2227
3SA0041 : Avoid joining with views.2418
See Also

Other Resources