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

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:

1-- Example of problematic insertion
2INSERT INTO DestinationTable
3SELECT * FROM SourceTable
4ORDER 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:

1-- Instead of this incorrect insertion
2-- INSERT INTO DestinationTable
3-- SELECT * FROM SourceTable
4-- ORDER BY ColumnName;
5
6-- Use this when retrieving data:
7SELECT * FROM DestinationTable
8ORDER BY ColumnName;

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

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

Performance Rules, Bugs

There is no additional info for this rule.

Other Resources