SA0079 : Avoid using column numbers in ORDER BY clause |
![]() |
Avoid using ordinal positions in the ORDER BY clause for better readability and maintainability.

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:
-
Identify the ORDER BY clause in your query that uses ordinal positions. This is typically a number instead of a column name.
-
Determine which column the ordinal position corresponds to by examining the SELECT list. This will help you identify the intended sort column.
-
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.

SQL
1/* Enter T-SQL script to test your analysis rule. */ 2SELECT OrderID, OrderDate FROM Orders 3ORDER BY 2 4 5SELECT OrderID, OrderDate FROM Orders 6ORDER BY OrderDate |

Message | Line | Column | |
---|---|---|---|
1 | SA0079 : Avoid using column numbers in ORDER BY clause. | 3 | 9 |
