SA0079 : Avoid using column numbers in ORDER BY clause

The topic describes the SA0079 analysis rule.

Message

Avoid using column numbers in ORDER BY clause

Description

The rule checks for ORDER BY clauses which reference select list column using the column number instead of the column name.

The column numbers in the ORDER BY clause as it impairs the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred by names instead of numbers. Consider the following example, in which the second query is more readable than the first one:

SQL
1SELECT OrderID, OrderDate FROM Orders
2ORDER BY 2
3
4SELECT OrderID, OrderDate FROM Orders
5ORDER BY OrderDate

How to fix

Review the ORDER BY clause and change the number with the column name or column alias name.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
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

Analysis Results

No violations found.

See Also

Other Resources