DF099: INSERT INTO statement with a SELECT subquery having an ORDER BY clause is used.

The topic describes the DF099 T-SQL code analysis rule.

Category

PERFORMANCE

Message

INSERT INTO statement with a SELECT subquery having an ORDER BY clause is used.

Description

To save resources and improve performance, it’s recommended to avoid unnecessary sorting. The order in which records are inserted into a table does not guarantee that they will be retrieved in the same order when selected.

Additional information

Sorting operations can be resource-intensive, consuming significant CPU and memory, especially on large datasets. Additionally, the physical order of records in a table is not guaranteed to match the order of retrieval in queries; the only way to ensure a specific order is by using the ORDER BY clause. Therefore, avoid assuming any implicit order of rows and only use ORDER BY when necessary for your application requirements. This approach helps minimize resource usage and speeds up query execution.

Noncompliant code example

INSERT INTO dbo.DemoTable
SELECT * FROM dbo.RawData
ORDER BY SomeDataField
GO

Compliant solution

INSERT INTO dbo.DemoTable
SELECT * FROM dbo.RawData
 
GO