Skip to content

BSL077 — Using 'SELECT TOP' without 'ORDER BY'

← All rules

Summary

Using 'SELECT TOP' without 'ORDER BY'

Identifiers

Field Value
Rule code BSL077
Compatible alias SelectTopWithoutOrderBy
Severity WARNING
Enabled by default Yes
Implemented Yes
Tags performance, maintainability

Behavior

  • The public identifier BSL077 and alias SelectTopWithoutOrderBy are stable.
  • The rule reports the cases documented on this page.
  • Suppressions and project configuration are applied before publication.
  • The rule requires neither an external analyzer nor network access.

Configuration and suppression

BSL### is the primary stable identifier. The compatible alias is accepted in select, ignore, and compatible block suppression comments.

[tool.onec-hbk-bsl]
select = ["BSL077"]
ignore = ["SelectTopWithoutOrderBy"]

All three suppression families support both a current line and a range. When an opening comment follows code, it affects only that line. Use any one form:

  • noqa:
Value = "example";  // noqa: BSL077
  • bsl-disable:
Value = "example";  // bsl-disable: BSL077
  • compatible BSLLS form:
Value = "example";  // BSLLS:SelectTopWithoutOrderBy-off

When the same opening comment is on a line by itself, it starts a range. Close it with the matching marker from the same family:

// noqa: BSL077
// code without this diagnostic
// noqa-enable: BSL077

// bsl-disable: BSL077
// code without this diagnostic
// bsl-enable: BSL077

// BSLLS:SelectTopWithoutOrderBy-off
// code without this diagnostic
// BSLLS:SelectTopWithoutOrderBy-on

To disable the rule until the end of the file, omit the closing noqa-enable, bsl-enable, or BSLLS:…-on marker.

Opening and closing markers must belong to the same family.

Description

Using the TOP N construct without specifying the sort order in ORDER BY or conditions in the WHERE section is fraught with unexpected results:

  • The order of the returned results may differ in different DBMSs
  • The order in different copies of information security will differ from the order expected by the developer

According to the standard, the absence of the sentence ORDER BY is justified only in cases where

  • the algorithm for processing query results does not rely on a specific order of records
  • the result of processing the executed request is not shown to the user
  • query result - obviously one record

In the above cases, it is recommended not to add the clause ORDER BY to the request body, as this leads to additional time-consuming execution of the request.

Diagnostic ignorance in code

During the analysis, constructions are considered erroneous:

  • Using TOP N in the union regardless of the presence of ORDER BY because ordering occurs after the union
  • Using TOP N where N> 1 if missing ORDER BY
  • Using TOP 1, if there is no ORDER BY and conditions in WHERE. This rule is disabled by default by a diagnostic option

Examples

SELECT TOP 1 // < - No error, there is a condition
   Reference.Link
OF
   Directory.Contractors AS Directory
WHERE
   Reference.Ref. IN (
       SELECT TOP 10 // < - Error, no sorting
           Link
       OF
           Reference, Contractors)

UNION ALL

SELECT TOP 10 // < - Error, no sorting (and cannot be)
   Reference.Link
OF
   Directory.Contractors AS Directory

UNION ALL

SELECT TOP 1 // < - Always error, even 1
   Reference.Link
OF
   Directory.Contractors AS Directory

SORT BY
   Link

Sources