Click or drag to resize

SA0109 : Avoid joining with subquery which has a TOP clause

The topic describes the SA0109 analysis rule.

Message

Avoid joining with subquery which has a TOP clause

Description

The rule checks for joined subqueries which return a limited by TOP clause number of rows.

Consider rewriting the query and joining directly with table sources of the subquery as it will let the SQL Server to use more optimal plan than the one when a subquery was joined. Note that when the subquery table sources are joined, you should ensure that the exact number of records are joined as when a subquery with TOP is used.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0109

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1SELECT d.DepartmentID,edh.StartDate,edh.EndDate,p.* 
 2FROM [HumanResources].[Department] d 
 3INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
 4                ON edh.[DepartmentID] = d.[DepartmentID] 
 5INNER JOIN ( SELECT TOP 1  p.* 
 6                         FROM [HumanResources].[Employee] e
 7                         INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
 8                         WHERE p.FirstName = 'David' AND LastName = 'Bradley' ) AS p 
 9            ON p.[BusinessEntityID] = edh.[BusinessEntityID] 
10
11SELECT d.DepartmentID,edh.StartDate,edh.EndDate, p.* 
12FROM [HumanResources].[Department] d 
13INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
14                ON edh.[DepartmentID] = d.[DepartmentID] 
15INNER JOIN [HumanResources].[Employee] e 
16                ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
17INNER JOIN [Person].[Person] p 
18                ON p.[BusinessEntityID] = e.[BusinessEntityID]
19WHERE p.BusinessEntityID = 16 -- The exact Person row is joined
20
21SELECT d.DepartmentID,edh.StartDate,edh.EndDate, p.* 
22FROM [HumanResources].[Department] d 
23INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
24                ON edh.[DepartmentID] = d.[DepartmentID] 
25INNER JOIN [HumanResources].[Employee] e 
26                ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
27INNER JOIN [Person].[Person] p 
28                ON p.[BusinessEntityID] = e.[BusinessEntityID]
29WHERE p.FirstName = 'David' AND 
30          p.LastName = 'Bradley' AND 
31      p.MiddleName = 'M' -- Additional condition to ensure the exact Person row is joined

Analysis Results

 MessageLineColumn
1SA0109 : Avoid joining with subquery which has a TOP clause.620
See Also

Other Resources