DF044: Joining with a subquery that has a TOP clause.

The topic describes the DF044 T-SQL code analysis rule.

Message

Joining with a subquery that has a TOP clause.

Description

Joining with subqueries that have a TOP clause may lead to a less-than-optimal query plan choice and poor performance.

Additional information

To improve query performance, rewrite the query to directly join with the table sources of the subquery. This approach allows SQL Server to generate a more optimal execution plan compared to joining with a subquery. However, when joining with the table sources of the subquery, ensure that the same number of records are joined as when using a subquery with TOP.

Noncompliant code example

SELECT
  be.*
FROM (SELECT TOP(100) * FROM Person.BusinessEntityAddress ORDER BY BusinessEntityID) bea
  INNER JOIN Person.BusinessEntity be
    ON bea.BusinessEntityID = be.BusinessEntityID
  INNER JOIN (SELECT TOP(100) * FROM Person.BusinessEntityContact ORDER BY BusinessEntityID) bec
    ON bec.BusinessEntityID = be.BusinessEntityID

Compliant solution

SELECT TOP(100)
  be.*
FROM Person.BusinessEntityAddress bea
  INNER JOIN Person.BusinessEntity be
    ON bea.BusinessEntityID = be.BusinessEntityID
  INNER JOIN Person.BusinessEntityContact bec
    ON bec.BusinessEntityID = be.BusinessEntityID
ORDER BY
  be.BusinessEntityID