SA0041 : Avoid joining with views

Introduction

Joining with views can negatively impact performance if not carefully managed.

Description

Using views in queries can introduce inefficiencies, especially if the developer is unaware of the underlying tables and relationships. This can result in unnecessary data retrieval and processing, affecting performance.

Example of a problematic query using a view:

SELECT * FROM MyView
JOIN AnotherTable ON MyView.SomeID = AnotherTable.SomeID;

The above query might seem straightforward, but if MyView is composed of multiple tables or includes complex joins, the performance can be degraded unexpectedly.

Can lead to execution of unnecessary joins when the view definition is not well understood.
May result in pulling more data than needed if the entire view is queried without filter optimizations.

How to fix

Avoid performance issues with joined views by eliminating redundant joins and optimizing the query structure.

Follow these steps to address the issue:

1.Identify any redundant joins in the query that involves the view. Examine the view definition to understand the tables and joins it includes. Use sp_helptext 'MyView' to review the view definition.

2.Optimize the query by rewriting it to exclude unnecessary joins. Consider whether all columns and tables in the view are needed.

3.If complex logic is encapsulated within the view, determine if it is more efficient to include specific joins or filters in the main query instead. Use targeted columns and rows by applying WHERE clauses appropriately.

4.Test the updated query performance with tools like SQL Server Management Studio’s execution plan feature. Adjust as necessary based on performance results.

Instead of joining with the view, directly use the view source table and view logic into the query:

SELECT Column1, Column2
FROM MyViewSourceTable S
JOIN AnotherTable A ON S.SomeID = A.SomeID
WHERE S.Condition = 'Value'

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.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- The SELECT statement will cause rule violation there are several views were joined in this query.
SELECT  e.*
FROM  HumanResources.vEmployee AS e
INNER JOIN HumanResources.vEmployeeDepartment AS ed
ON  ed.EmployeeID = e.EmployeeID
, HumanResources.vEmployeeDepartmentHistory AS edh
, Employee AS a

--  Rows are selected form the HumanResources.vEmployee view only.
SELECT  e.*
FROM  HumanResources.vEmployee AS e

Analysis Results

	Message	Line	Column
1	SA0041 : Avoid joining with views.	4	21
2	SA0041 : Avoid joining with views.	5	26
3	SA0041 : Avoid joining with views.	7	17

Analysis Rules