DF033: The SELECT TOP statement is used without the ORDER BY clause.

Last modified: December 25, 2024

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

Category

BEST PRACTICE

Message

The SELECT TOP statement is used without the ORDER BY clause.

Description

It is recommended that you always use an ORDER BY clause in SELECT TOP statements to specify the order in which the rows are returned. This helps ensure that the result set is predictable and consistent.

Additional information

If the ORDER BY clause is not specified, the order of returned data will depend on different factors. These include the order and type of table joins (nested loop, hash join, merge join), whether indexes are used for data selection, whether parallelisation is used, whether intermediate data is stored in tempdb, etc.

Noncompliant code example

SELECT TOP (100) *
FROM dbo.DemoTable

Compliant solution

SELECT TOP (100) *
FROM dbo.DemoTable
ORDER BY FirstName ASC