SA0041 : Avoid joining with views |
 |
The topic describes the SA0041 analysis rule.
MessageAvoid joining with views
DescriptionThe 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.
How to fixReview the query and the joined views for redundant joins and if found, consider rewriting the query.
ScopeThe rule has a Batch scope and is applied only on the SQL script.
ParametersRule has no parameters.
RemarksThe rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix20 minutes per issue.
CategoriesDesign Rules, Bugs
Additional InformationThere is no additional info for this rule.
Example Test SQL|
SQL
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| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0041 : Avoid joining with views. | 21 | 27 |
| 2 | SA0041 : Avoid joining with views. | 22 | 27 |
| 3 | SA0041 : Avoid joining with views. | 24 | 18 |
See Also