SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function
Introduction
Section titled “Introduction”The misuse of ORDER BY in certain T-SQL constructs can lead to unexpected query results.
Description
Section titled “Description”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 BYCREATE VIEW SampleView ASSELECT * FROM EmployeesORDER 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 BYis explicitly used in the query statement. -
Developers may assume guaranteed ordering, missing the necessary
ORDER BYin their top-level queries, leading to potential data inconsistency in applications.
How to fix
Section titled “How to fix”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 BYCREATE VIEW SampleView ASSELECT * FROM Employees;
-- Explicitly order data when selecting from the viewSELECT * FROM SampleViewORDER BY LastName;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”5 minutes per issue.
Categories
Section titled “Categories”Performance Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL” CREATE FUNCTION TestTableValuedFunction ()
RETURNS TABLE RETURN
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate FROM Sales.SalesOrderDetailORDER BY SalesOrderIDAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function. | 16 | 0 |