INSERT INTO ..SELECT ..ORDER BY into remote tables

Post date: Jul 10, 2010 2:00:58 AM

I ran into this issue at work the other day on SQL 2005. I posted this on the SQL Server forum and the thread can be accessed here. But for those who'd like to avoid the hop, here's the problem statement.

Basically what I am noticing is that when the target of an INSERT INTO ...SELECT ORDER BY happens to be a remote table, the ordering gurantees are not being followed. That's either the issue or its a flaw in my understaning..so let me illustrate with an actual example I was able to confirm this behavior with..

-- On local Db, create source and dest tables

create table tempDest ( KeyValue INT IDENTITY(1,1),

Name NVARCHAR(20), SeqID uniqueidentifier DEFAULT(NEWSEQUENTIALID()))

create table tempSource ( KeyValue INT IDENTITY(1,1),

Name NVARCHAR(20), SeqID uniqueidentifier DEFAULT(NEWSEQUENTIALID()))

-- On remote db, create dest table

create table tempDest ( KeyValue INT IDENTITY(1,1),

Name NVARCHAR(20), SeqID uniqueidentifier DEFAULT(NEWSEQUENTIALID()))

-- Populate source table on local db

insert into tempSource (Name) values ('Rec3')

insert into tempSource (Name) values ('Rec2')

insert into tempSource (Name) values ('Rec1')

select * from tempSource order by name asc

KeyValue Name SeqID

----------- -------------------- ------------------------------------

3 Rec1 7DCDE7DD-5E84-DF11-AF5B-005056830896

2 Rec2 7CCDE7DD-5E84-DF11-AF5B-005056830896

1 Rec3 7BCDE7DD-5E84-DF11-AF5B-005056830896

--Insert into Destination table on remote server/db

insert into RemoteServer.DestDB.schemaname.tempDest (Name)

select Name

from tempSource

order by name asc

Exec plan is as follows ( notice that no ordering is done)

StmtText

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Remote Insert(SOURCE:(ROGUE), OBJECT:("DestDB"."schemaname"."tempDest"), SET:([RemoteServer].[DestDB].[schemaname].[tempDest].[Name] = [SourceDB].[schemaname].[tempSource].[Name],[RemoteServer].[DestDB].[schemaname].

[tempDest].[KeyValue] = default,[RemoteServer].[DestDB].[schemaname].[tempDest].[SeqID] =

default))

|--Table Spool

|--Table Scan(OBJECT:([SourceDB].[schemaname].[tempSource]))

--Insert into Destination table on local server/db

insert into tempDest (Name)

select Name

from tempSource

order by name asc

Exec plan is as follows ( notice that the ordering is obeyed )

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Table Insert(OBJECT:([SourceDB].[schemaname].[tempDest]), SET:([SourceDB].[schemaname].[tempDest].[Name] = [SourceDB].[schemaname].[tempSource].[Name],[SourceDB].[schemaname]. [tempDest].

[KeyValue] = [Expr1008],[SourceDB].[schemaname].[tempDest].[SeqID] = [Expr1009]))

|--Compute Scalar(DEFINE:([Expr1008]=getidentity((1066276516),(18),NULL), [Expr1009]=newsequentialid()))

|--Top(ROWCOUNT est 0)

|--Sort(ORDER BY:([SourceDB].[schemaname].[tempSource].[Name] ASC))

|--Table Scan(OBJECT:([SourceDB].[schemaname].[tempSource]))

--Select from remote destination table order by identity

select * from RemoteServer.DestDB.schemaname.tempDest order by Keyvalue asc

KeyValue Name SeqID

----------- -------------------- ------------------------------------

1 Rec3 46725622-6084-DF11-9855-001D09691873

2 Rec2 47725622-6084-DF11-9855-001D09691873

3 Rec1 48725622-6084-DF11-9855-001D09691873

--Select from local destination table order by identity

select * from tempDest order by Keyvalue asc

KeyValue Name SeqID

----------- -------------------- ------------------------------------

1 Rec1 2BD7B224-6084-DF11-AF5B-005056830896

2 Rec2 2CD7B224-6084-DF11-AF5B-005056830896

3 Rec3 2DD7B224-6084-DF11-AF5B-005056830896

drop table tempDest

drop table tempSource

So as you can see, the ordering on the INSERT to the remote server is not obeyed and seems to go against what was suggested in the SQL Server engine teams blog at http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx on ordering guarantees as well as what was published in the MS KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 . The local INSERT obeys the order and is what I want and would have expected to happen.

Can someone explain to me if I am understanding things correctly? Is this expected behavior? If not , are there any workarounds..? This issue popped up after we migrated to SQL 2005 and did not occur in SQL 2000 and currently is causing some relatively major issues for us and so any help will be appreciated.

There were a few workarounds posted on the forums like using the TOP statement to force a sort while inserting into the remote table and a few other suggestions but I wanted some sort of official word on this - it just did not seem right. Anyway, I decided to utilize some free Microsoft Email support through work and had our systems guy email Microsoft. I will post the responses in another post..