I use a unidirectional Replication of a database.
In the Database I have a column with the datatype "timestamp" (not datetime).
When the replication is initiated it writes table after table to the subscriber.
My question is: Is the data within a table writen to the subscriber in the same order as it was created in the published database?
So is the timestamp in a table of the subscriber also usable as a sort order as it was on the publisher e.g. for last inserted and updated records? Becouse unfortunatly I do not have a column like EditDateTime.
Best Answer
I did a test, and it seems that you are at the mercy of how the BCP file was generated for the snapshot. I.e., in what order did SQL server happened to read the rows when generating the snapshot file (bcp file).
I.e., the same story as "Can we guarantee an ordering without ORDER BY?" The answer (of course) being "no".
I did a test and created a clustered index on the table as DESC, just to increase the likelihood that the data was to be read in a "different" order when producing the snapshot.
Then I did a few queries to test, and my fears were confirmed. The sequence for the ts column was indeed dependent on the order which were used to read the data when generating the BCP file.
So I guess this is one of those "Do you feel lucky?" :-)
I created both the pub and sub on the same SQL Server, using two databases: a and b. Below is a rough script of what I was using, in case you want to play with it.
Having said that, there is an option for the pub to convert timestamp to binary(8). As far as I can imagine, that should keep the data, and you'd of course lose the ts attribute and functionality on the sub.
After changing this attribute, the values did indeed come out the same for both my a and b table.
So, short story seems to be to convert your timestamps to a passive binary(8) in the replication property and you'll keep the values (and by that the ordering as well).