DTS Data Transfers & Guaranteed Insertion Order?

  • Does anyone know if during a data transfer through a "Transform Data Task" guarantee the destination table maintains the same row order as the source?  I had an abberant row that was inserted out of order, relative to the source table.

    (Everything is default.  Just doing a straight table to table copy.  Collation for both instances are the same.)

    Thanks,

    Thuan

  • Only if the source table has the clustered index or order by clause is specified other wise it will not maintain row order...

     

    MohammedU
    Microsoft SQL Server MVP

  • Only if the source and destination table have the same clustered index, can we assume some order. Even then, after the data transfer, if the index is disabled from the destination table, no order can be guaranteed.

    The basic premise is "sets are not ordered"

     

  • These are inserts the same as any other table. No order is every guarenteed. You do not know when things are inserted, unless you are timestamping, and then you only know to the millisecond. Or partial millisecond.

    A clustered index will order the data in the order of the index physically, but this still does not guarentee retrieval in this order. It almost always is, but it's no guarenteed.

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

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