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

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:

SQL
Copy
1SELECT * FROM Employees 
2ORDER 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:

SQL
Copy
1SELECT * FROM Employees 
2ORDER 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.
Categories

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE PROCEDURE HumanResources.uspGetAllEmployees
 2AS 
 3SET NOCOUNT ON;
 4
 5SELECT LastName,
 6       FirstName,
 7       JobTitle,
 8       Department
 9FROM HumanResources.vEmployeeDepartment
10ORDER BY 2 -- numeric constant is allowed
11
12SELECT au_id
13FROM dbo.authors
14ORDER BY 'a',  -- string constants are deprecated
15         NULL  -- NULL is deprecated
16
17
18SELECT au_id
19FROM dbo.authors
20ORDER BY 'a',  -- string constants are deprecated and NULL-s are deprecated, 
21                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
See Also

Other Resources

Analysis Rules