SA0109 : Avoid joining with subquery which has a TOP clause

The topic describes the SA0109 analysis rule.


Avoid joining with subquery which has a TOP clause


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

How to fix

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.


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


Rule has no parameters.


The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
 1SELECT d.DepartmentID,edh.StartDate,edh.EndDate,p.* 
 2FROM [HumanResources].[Department] d 
 3INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
 4                ON edh.[DepartmentID] = d.[DepartmentID] 
 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] 
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
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
  Message Line Column
1 SA0109 : Avoid joining with subquery which has a TOP clause. 6 20
See Also

Other Resources