SA0019 : TOP clause used in a query without an ORDER BY clause

The topic describes the SA0019 analysis rule.

Message

TOP clause used in a query without an ORDER BY clause

Description

This rule checks for usages of TOP in queries without an ORDER BY clause.

It is generally recommended to specify sort criteria when using TOP clause. Otherwise, the results produced will be plan dependent and may lead to undesired behavior.

How to fix

Review the query, add ORDER BY to ensure the results will be sorted and the expected TOP rows to be returned.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1SELECT TOP 20
 2           LastName,
 3           FirstName,
 4           JobTitle,
 5           Department
 6FROM       HumanResources.vEmployeeDepartment
 7-- ORDER BY LastName ASC
 8
 9
10SELECT TOP 20
11           LastName,
12           FirstName,
13           JobTitle,
14           Department
15FROM       HumanResources.vEmployeeDepartment
16ORDER BY LastName ASC
17
18SELECT 
19        F.Code, 
20        F.CustNum, 
21        SupplierCode = ((SELECT TOP 1 S.SupplierCode FROM Supplier S WHERE S.FacilityCode = F.FacilityCode))
22FROM Facility F;

Analysis Results
  Message Line Column
1 SA0019 : TOP clause used in a query without an ORDER BY clause. 1 7
2 SA0019 : TOP clause used in a query without an ORDER BY clause. 21 25
See Also

Other Resources