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

The topic describes the SA0245 analysis rule.

Message

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

Description

The rule checks T-SQL code for usages of ORDER BY clause in view-s and single statement inline table valued functions.

If there are TOP or FOR XML clauses also specified, the ORDER BY clause is valid and a rule violation will not be reported.

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

How to fix

Remove the redundant ORDER BY clause.

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.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1 CREATE FUNCTION TestTableValuedFunction ()
 2
 3 RETURNS TABLE
 4 RETURN
 5
 6SELECT SalesOrderID,   
 7     SalesOrderDetailID,  
 8     CarrierTrackingNumber,   
 9     OrderQty,   
10     ProductID,   
11     SpecialOfferID,   
12     UnitPrice,   
13     UnitPriceDiscount,  
14     rowguid,   
15     ModifiedDate FROM Sales.SalesOrderDetail 
16ORDER BY SalesOrderID
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
See Also

Other Resources

Analysis Rules