DTS reordering records when copying from one db instance to another identical one

Question

DTS reordering records when copying from one db instance to another identical one

Sue Turner

Grasshopper

Points: 14
More actions
November 16, 2005 at 3:18 am

#166057

Can anybody tell me why, when I use DTS to copy my data from one database instance to another identical instance, the order in which my records are inserted is changed? For example when I run a select query on the first table, no ordering, no joins, just a basic select, i get the info in one way, but when I run the same select query on the second table, the info is sorted differently. If I check through the GUI and look at the tables directly, then I still see that the records have been inserted in a different order. I used standard DTS import, with no transformations. Did not schedule the task, but ran immediately.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Firstly, there is no guaranteed order that data will be returned in when selecting unless you use an ORDER BY clause. You see the same order on your simple select each time on database A because the table pages happen to be in that order. DTS does a simple select and then an insert - the target server has no knowledge (and shouldn't need any knowledge) of the storage order, etc from the first server...

Essentially, SQL is a relational database - not a file-system database like DBase where you have row numbers, etc... SQL will store the data in what it sees as an efficient way, and return the data in what it sees as efficient - if you want it to return the data in a certain order, you have to tell it.

For example, if you did a

select * from MyTable where column1 = xyz

and then did another

select * from MyTable where column2 = abc

assuming that columns column1 & column2 have their own indexes, you should see the data returned in a different order because SQL would have seen it to be more efficient to use the column index and return the data as it appears in the column index...

Hope that explains why you needn't be worried about what you saw, why it happens and perhaps a bit of an insight into the way the DB engine works.

Sue Turner Grasshopper Points: 14 More actions · Answer 2

Hi Ian

Thanks for the quick reply. I suspected that was the situation since DTS is not intelligent in and of itself unless you tell it to be so.

I guess the answer to my problem is to use a query and order it the way I want it to go.

Rgds

Sue

david russell-253790 SSCarpal Tunnel Points: 4042 More actions · Answer 3

Good answer. Let me hear comments on the following scenario.

We have data in a Sybase ASA database that does not have a primary key. We are replicating this data to a Sybase Replication Server (also ASA) and as a result we need the primary key. The tables are going to be unloaded, the primary key identified (added, auto increment), and the data reloaded to initialize the PK.

From the replicated server we are using Sybase "Direct Connect" to put the same data (replicated from ten places into one) into MS SQL Server. In order to cut down on network traffic the data files created with the unload (and subsequently reloaded into ASA) are going to be shipped to the SQL Server side and loaded manually. Remember, these flat files of data do NOT contain the primary key that was necessary to do the replication.

Nobody initially remembered that there was no primary key in these files, and Sybase Replication Server/Direct Connect will need the PK to know that these files do not need to be replicated. The proposed solution to add another sequential number to the rows in the files (either on unload, or the load on the other side), and that the numbers will be the same... to repeat that, the autoincrement column in ASA created by loading the flat files will be the same as sequential numbers assigned when loading the SQL database side.

I agree 100% that you cannot count on the order without an order-by clause... seems to me like this proposed technique is flawed; but I have yet to make the argument.

What do others think about this?

Cheers,

david russell

Ian Yates SSCoach Points: 19738 More actions · Answer 4

I should state that if you have an identity column on a table and insert the rows in a particular order then you will see them ordered sequentially in the identity column.

For example - run the following...

declare @x table(id int identity(1,1), value varchar(50))
insert into @x(value)
select name from master.dbo.sysdatabases
order by name

declare @y table(id int identity(1,1), value varchar(50))
insert into @y(value)
select name from master.dbo.sysdatabases
order by dbid

select * from @x

select * from @y

I just chose the sysdatabases table as a convenient source of data. Note that you can order the data that is inserted and the identity reflects this.

So this might be of some help - Can sybase direct connect insert the data in a particular order?

On my earlier post regarding DTS, if you were after a similar thing (to have an identity column indicate the ordering of the source data), you could use the same technique but instead of selecting a table to insert, you would have to do a

select * from

order by [my order columns]

Hope that helps you out