Home

Queries that use TOP should have an ORDER BY

Description

    The SQL Server code rule "Queries that use TOP should have an ORDER BY" requires that when using the TOP clause in a query, an ORDER BY clause must also be included. This ensures that the results of the query are in a predictable order. Without an ORDER BY clause, the order of the results is not guaranteed. This rule is in place to ensure that the results of the query are consistent and predictable.

Key Benefits

  • Faster Execution: Queries that use TOP with OrderBy can execute faster than queries that do not, as they are limited to a certain number of results.
  • More Control: Using TOP with OrderBy allows you to have more control over the results returned by your query, as you can specify the exact number of results you want.
  • Accurate Results: By using an ORDER BY clause with TOP, you can ensure that the results returned are accurate and in the order you want.

 

Non-compliant Code Example

SELECT TOP(10)JobTitle, HireDate   --Non compliant code (Query use TOP without Order By clause, which will select the first 10 random employees.)
FROM HumanResources.Employee;  
GO  

Compliant Code Example

SELECT TOP(10)JobTitle, HireDate   --Compliant code (Query use TOP with Order By clause, which will select the first 10 employees hired most recently.  )
FROM HumanResources.Employee  
ORDER BY HireDate DESC;  
GO
Visual Expert 2024
 VETSQLRULE13