Skip to content

SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function

The misuse of ORDER BY in certain T-SQL constructs can lead to unexpected query results.

Using the ORDER BY clause within views, inline table-valued functions, derived tables, or subqueries might not behave as developers expect. This is because ORDER BY is often misinterpreted as ensuring the order of the results, even though this is only true when used directly in queries retrieving data. When ORDER BY is part of those constructs, it primarily affects the sorting for any TOP or FOR XML clauses that follow but doesn’t dictate the final order of the dataset returned by the higher-level query.

For example:

-- Example of problematic view with ORDER BY
CREATE VIEW SampleView AS
SELECT * FROM Employees
ORDER BY LastName;

The example above illustrates a view designed to return employees ordered by LastName . However, the use of ORDER BY in the view does not guarantee that results from the view will be ordered by LastName when queried. This often leads to misunderstandings about the behavior of views and inline functions.

  • Queries against the view may return results in any order unless ORDER BY is explicitly used in the query statement.

  • Developers may assume guaranteed ordering, missing the necessary ORDER BY in their top-level queries, leading to potential data inconsistency in applications.

Remove unnecessary ORDER BY clauses to ensure predictable query results.

Follow these steps to address the issue:

1.Identify views, subqueries, inline table-valued functions, or derived tables that improperly use ORDER BY .

2.Remove the ORDER BY clause from these constructs to prevent any misleading order assumptions.

3.In the query retrieving data from these constructs, explicitly specify the ORDER BY clause if a specific order is required.

For example:

-- Corrected view without ORDER BY
CREATE VIEW SampleView AS
SELECT * FROM Employees;
-- Explicitly order data when selecting from the view
SELECT * FROM SampleView
ORDER BY LastName;

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.

5 minutes per issue.

Performance Rules, Bugs

There is no additional info for this rule.

CREATE FUNCTION TestTableValuedFunction ()
RETURNS TABLE
RETURN
SELECT SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
ProductID,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
rowguid,
ModifiedDate FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID
  Message Line Column
1 SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function. 16 0

Analysis Rules