Существует очень ра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
Подписаться