Click or drag to resize

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.

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.

Categories

Performance Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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

 MessageLineColumn
1SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function.160
See Also

Other Resources