Click or drag to resize

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.

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:SA0019

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
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

 MessageLineColumn
1SA0019 : TOP clause used in a query without an ORDER BY clause.17
2SA0019 : TOP clause used in a query without an ORDER BY clause.2125
See Also

Other Resources