INSERT INTO table with ORDER BY

The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Inserting data into a column store table can significantly improve performance. However, row store tables are either unordered (heaps) or ordered by the clustering key, so the ORDER BY is meaningless. Use a ROW_NUMBER() window clause if you need to impose a particular order on rows in the table, especially if you need to allow subsequent insertions into the table.

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard

The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() Window clause instead, if you need to impose a particular order on rows in the table, via a query, especially if you need to allow subsequent insertions into the table. For temporary tables or table variables that will have no subsequent insertions, the current order can be maintained via an IDENTITY field in the target table, but it is an anachronism now that there are better and more versatile ways of doing it.

See also: SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)