Skip to content

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

Inserting records in a specific order using ORDER BY may not guarantee retrieval in that order.

In SQL Server, inserting data into tables with an ORDER BY clause present in INSERT INTO SELECT or SELECT INTO statements does not guarantee the order of rows when retrieved later. This can lead to incorrect assumptions about data order during application processing.

For example:

-- Example of problematic insertion
INSERT INTO DestinationTable
SELECT * FROM SourceTable
ORDER BY ColumnName;

The use of ORDER BY with INSERT INTO operations is generally redundant because it does not preserve ordering in table storage; only retrieval operations using SELECT ... ORDER BY ensure the returned rows are ordered as intended.

  • Misinterpretation that data is stored in a specific order as it appears in retrieval queries.

  • Potential performance impacts due to mistaken reliance on ORDER BY during insertion.

Avoid relying on the ORDER BY clause during data insertion to ensure correct data retrieval order. Instead, sort results using ORDER BY when querying the data.

Follow these steps to address the issue:

1.Remove the ORDER BY clause from any INSERT INTO SELECT or SELECT INTO statements as it does not affect the physical order of data in the table.

2.When retrieving data from the table, use a SELECT statement with an ORDER BY clause to ensure the desired row order in the result set.

3.Verify that any application logic depending on row order processing adjusts to using retrieval statements with the appropriate ORDER BY clause.

For example:

-- Instead of this incorrect insertion
-- INSERT INTO DestinationTable
-- SELECT * FROM SourceTable
-- ORDER BY ColumnName;
-- Use this when retrieving data:
SELECT * FROM DestinationTable
ORDER BY ColumnName;

The rule has a Batch scope and is applied only on the SQL script.

Name Description Default Value
IgnoreTempTableTargets The parameter specifies whether to ignore insert target tables, which are temporary tables. no
IgnoreTableVariableTargets The parameter specifies whether to ignore insert target tables, which are table variables. no
IgnoreTargetsWithIdentityColumn The parameter specifies whether to ignore insert target tables, which have identity column defined. no
IgnoreTempTargetTablesWithIdentityColumn The parameter specifies whether to ignore insert target tables, which are temporary tables and have identity column defined. no
IgnoreTableVariablesWithIdentityColumn The parameter specifies whether to ignore insert target tables, which are table variables and have identity column defined. no

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

5 minutes per issue.

Performance Rules, Bugs

There is no additional info for this rule.

INSERT INTO Sales.SalesOrderDetailHistory
SELECT SalesOrderID, OrderQty, UnitPrice
FROM Sales.SalesOrderDetail
ORDER BY ModifiedDate DESC
SELECT SalesOrderID, OrderQty, UnitPrice
INTO Sales.SalesOrderDetail
FROM Sales.SalesOrderDetail
ORDER BY ModifiedDate DESC
  Message Line Column
1 SA0237 : Ordering of the result set before inserting it into a table is pointless. 4 0
2 SA0237 : Ordering of the result set before inserting it into a table is pointless. 9 0

Analysis Rules