SA0079 : Avoid using column numbers in ORDER BY clause

Introduction

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

Description

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:

-- Example of problematic query
SELECT OrderID, OrderDate FROM Orders
ORDER 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:

-- Improved query
SELECT OrderID, OrderDate FROM Orders
ORDER 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.

How to fix

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:

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

-- Improved query
SELECT OrderID, OrderDate FROM Orders
ORDER BY OrderDate;

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.

Additional Information

There is no additional info for this rule.

Example Test SQL

/* Enter T-SQL script to test your analysis rule. */
SELECT OrderID, OrderDate FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate FROM Orders
ORDER BY OrderDate

Analysis Results

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

Analysis Rules