INSERT + ORDER BY

by Alexey Knyazev 26. января 2011 00:42

Order by

Существует очень раcпространенный миф, что конструкция insert into ... select ... from ... order by ... гарантирует последовательность вставки данных в таблицу согласно условию order by. Но это заблуждение, т.к. мы не можем гарантировать последовательность физической вставки данных. Сиквел сам (по своему, неведанному нам сценарию) определяет, как данные попадут в таблицу, последовательно или параллельно и какими кусками определяет оптимизатор. При этом кляуза (clause) просто игнорируется.
Но существует очень интересная особенность, когда в таблице, в которую осуществляется вставка, есть поле IDENTITY


Если перенос данных осуществлять через конструкцию SELECT ... INTO:

SELECT Col1, Col2, ID=IDENTITY (int, 1, 1)
INTO NewTable
FROM OldTable
Order By Col1

То данные могут расположиться в новой таблице в совершенно произвольном порядке, например:

Col1      Col2       ID
-------   --------   --------
1         A          4
2         Z          2
7         G          5
11        F          3
17        I          1

Однако, если вставку делать в таблицу, которая создана заранее и содержит поле IDENTITY, то конструкция:
INSERT INTO NewTable (Col1, Col2) 
SELECT Col1, Col2 FROM OldTable ORDER BY Col1 
Гарантирует, что данные в таблице расположатся в нужном порядке:
ID (identity)    Col1     Col2
-------------    ------   ------
1                1         S
2                2         z
3                7         G
4                11        F
5                17        I

При этом мы не повлияли на физический порядок вставки данных, дело в том, что значения для поля IDENTITY были созданы до самой вставки и их порядок соответствует условию ORDER BY.
К сожалению в BOL описано лишь это:
Использование предложения ORDER BY с инструкцией SELECT...INTO для вставки строк из другого источника,
не гарантирует вставку строк в указанном порядке.

В остальном официальный хелп молчит, хотя подобные вопросы очень часто поднимаются, как на наших форумах по MS SQL Server, так и на англоязычных...

Тут же хочу сказать ещё об одной особенности. Если версия вашего SQL Server ниже 10-ой (SQL Server 2008), то подобное поведение отличается от вставки в локальную (на текущем сервере) таблицу и в таблицу расположенную на удаленном сервере (Linked Server). При вставки данных в таблицу (с полем IDENTITY) на удаленном сервере, порядок расположения данных опять не гарантирован.
Эта ситуация признана багом и была "fixed in SQL 2008". Но не отчаивайтесь, если у вас SQL Server 2000 или 2005, т.к. эту особенность можно обойти, если указать в SELECT предложение TOP c числом заведомо больше, чем данных в таблице-источнике.
INSERT INTO RemoteServer.TestDB.SchemaName.NewTable (Col1, Col2) 
SELECT TOP 1000000000 Col1, Col2 FROM OldTable ORDER BY Col1 
Тем самым порядок следования перенесенных данных будет такой же, как при вставке в локальную таблицу.



Ссылки по теме:
http://support.microsoft.com/kb/273586/en-us/
http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
http://sites.google.com/site/venkatrajagopal/sqlblog/msftsupportresponsetoinsertintoselectissue

Tags: , , , , ,

SQL Server

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading