SA0079 : Avoid using column numbers in ORDER BY clause

The use of ordinal positions (column numbers) in the ORDER BY clause of a T-SQL query can cause confusion and make the code less readable. In SQL Server, you can specify the sort order of your query results by referencing the column names directly rather than using their numbers in the select list. This practice helps avoid unintended changes in query behavior when the select list is modified.

For example:

1-- Example of problematic query
2SELECT OrderID, OrderDate FROM Orders
3ORDER BY 2

Using a column number like 2 in the ORDER BY clause is prone to errors. If the order of columns in the select list changes, the sort order may no longer reflect the intended results. Instead, it is advisable to use the column name, as shown below:

1-- Improved query
2SELECT OrderID, OrderDate FROM Orders
3ORDER BY OrderDate

When using the column name:

• The SQL statement becomes easier to understand for anyone reading the code.

• Future modifications to the select list do not affect the sorting logic.

Avoid using ordinal positions in the ORDER BY clause for improved readability and maintainability.

Follow these steps to address the issue:

1. Identify the ORDER BY clause in your query that uses ordinal positions. This is typically a number instead of a column name.

2. Determine which column the ordinal position corresponds to by examining the SELECT list. This will help you identify the intended sort column.

3. Replace the ordinal position in the ORDER BY clause with the column name or column alias that accurately represents the intended sort column.

For example:

1-- Example of problematic query
2SELECT OrderID, OrderDate FROM Orders
3ORDER BY 2;
4
5-- Improved query
6SELECT OrderID, OrderDate FROM Orders
7ORDER BY OrderDate;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Code Smells

There is no additional info for this rule.

Other Resources