SA0237 : Ordering of the result set before inserting it into a table is pointless

The topic describes the SA0237 analysis rule.

Message

Ordering of the result set before inserting it into a table is pointless

Description

This rule checks T-SQL code for INSERT INTO statements with a SELECT statement having an ORDER BY clause or for SELECT INTO statements having ORDER BY clause.

The order of inserting records in a relational table does not guarantee that the records will be returned in the same order when selected.

To select the result set in a specific order use the ORDER BY clause of the SELECT statement.

How to fix

Do not order the result set before inserting it into a table. Sort the result set using the ORDER BY clause of the query statement.

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
 1INSERT INTO Sales.SalesOrderDetailHistory
 2SELECT SalesOrderID,   
 3     SalesOrderDetailID,  
 4     CarrierTrackingNumber,   
 5     OrderQty,   
 6     ProductID,   
 7     SpecialOfferID,   
 8     UnitPrice,   
 9     UnitPriceDiscount,  
10     rowguid,   
11     ModifiedDate FROM Sales.SalesOrderDetail 
12WHERE ModifiedDate > getdate() - 1
13ORDER BY SalesOrderID
14
15SELECT SalesOrderID,   
16     SalesOrderDetailID,  
17     CarrierTrackingNumber,   
18     OrderQty,   
19     ProductID,   
20     SpecialOfferID,   
21     UnitPrice,   
22     UnitPriceDiscount,  
23     rowguid,   
24     ModifiedDate 
25INTO Sales.SalesOrderDetail
26FROM Sales.SalesOrderDetail 
27WHERE ModifiedDate > getdate() - 1
28ORDER BY SalesOrderID
29
30INSERT INTO Sales.SalesOrderDetailHistory
31SELECT SalesOrderID,   
32     SalesOrderDetailID,  
33     CarrierTrackingNumber,   
34     OrderQty,   
35     ProductID,   
36     SpecialOfferID,   
37     UnitPrice,   
38     UnitPriceDiscount,  
39     rowguid,   
40     ModifiedDate FROM Sales.SalesOrderDetail 
41WHERE ModifiedDate > getdate() - 1
42ORDER BY SalesOrderID
43
44INSERT INTO Sales.SalesOrderDetailHistory
45SELECT TOP 1 SalesOrderID
46     ModifiedDate FROM Sales.SalesOrderDetail 
47WHERE ModifiedDate > getdate() - 1
48ORDER BY SalesOrderID
49
50SELECT TOP 1 SalesOrderID
51INTO #SalesOrderDetailHistory
52FROM Sales.SalesOrderDetail 
53WHERE ModifiedDate > getdate() - 1
54ORDER BY SalesOrderID
Analysis Results
  Message Line Column
1 SA0237 : Ordering of the result set before inserting it into a table is pointless. 13 0
2 SA0237 : Ordering of the result set before inserting it into a table is pointless. 28 0
3 SA0237 : Ordering of the result set before inserting it into a table is pointless. 42 0
See Also

Other Resources

Analysis Rules