SA0018 : Support for constants in ORDER BY clause have been deprecated

Introduction

Using constants in an ORDER BY clause leads to unpredictable sorting and is deprecated in SQL Server.

Description

In SQL Server, employing constants as sort columns within an ORDER BY clause can result in ambiguous orders and is considered a deprecated practice. This approach does not provide any actual sorting logic relevant to the data and can lead to unreliable query results.

Problematic query with constant in ORDER BY :

SELECT * FROM Employees
ORDER BY 1;

In this example, the constant 1 is used as the sorting column, which does not correspond to an explicit column in the SELECT statement. This can cause confusion about the intention behind the sorting and leads to ambiguous behavior in query results.

Using a constant does not clearly specify which column should be used for sorting, leading to unreliable or unexpected result order.
This practice is deprecated, meaning future SQL Server updates may completely remove support for such queries, potentially breaking applications.

How to fix

To ensure reliable query sorting and adhere to best practices, avoid using constants as sort columns in the ORDER BY clause.

Follow these steps to address the issue:

1.Identify the ORDER BY clause in your query that contains a constant. For example, ORDER BY 1 is a problematic usage.

2.Determine the actual column by which the data should be sorted. This requires understanding the context and purpose of the query.

3.Replace the constant with the explicit column name or alias that reflects the intended sorting logic. Update your query accordingly.

Corrected query with specified column for sorting:

SELECT * FROM Employees
ORDER BY EmployeeName;

Scope

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

Parameters

Name	Description	Default Value
IgnoreOredrByInOverClause	Parameter specifies if to ignore order by a constant when it is used inside an OVER clause.	yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;

SELECT LastName,
       FirstName,
       JobTitle,
       Department
FROM HumanResources.vEmployeeDepartment
ORDER BY 2 -- numeric constant is allowed

SELECT au_id
FROM dbo.authors
ORDER BY 'a',  -- string constants are deprecated
         NULL  -- NULL is deprecated


SELECT au_id
FROM dbo.authors
ORDER BY 'a',  -- string constants are deprecated and NULL-s are deprecated,
    NULL   -- but will be ignored because of the rule suppression mark here -> IGNORE:SA0018

Analysis Results

	Message	Line	Column
1	SA0018 : Support for constants in ORDER BY clause have been deprecated.	14	9
2	SA0018 : Support for constants in ORDER BY clause have been deprecated.	15	9
3	SA0018 : Support for constants in ORDER BY clause have been deprecated.	20	9

Analysis Rules