Basic SQL Execution Question

I have a table that contains a history of record changes within another table. It may have rows that look like this:

DML_Type Record_ID Record_Value DateTime
Insert 1 A Today @. 11AM
Update 1 B Today @. 11:15AM
Update 1 C Today @. 11:22AM
Delete 1 C Today @. 11:25AM
Insert 2 A Today @. 11AM

Update 2 B Today @. 11:15AM

Update 2 C Today @. 11:22AM

I've inherited a stored procedure that migrates these changes to another database in another schema. The proc is using a cursor to perform each operation in sequence.

If I perform the migration in 3 steps (inserts, updates, deletes) - Can I be sure that the updates will occur in the correct sequence if ordered by DateTime or is there a possibility that SQL Server will parellize the operation and I'd end up with incorrect data? For example, is there a possibility using the T-SQL below that I'd end up with a record_ID 2 and a value of B?

UPDATE
destination table
SET
field1 = field,
...
FROM
SELECT
Record_ID,
Record_Value
FROM
sourceTable st
WHERE
DML_Type = 'Update'
ORDER BY
DateTime
WHERE
dt.Record_ID = st.Record_ID

Dan:

Is Record_ID unique in the [destination table]?

Dave

|||Yes. Record_ID is unique in the destination table. It's amusing that you ask this because the problem I'm dealing with can be completely avoided by using the state of the record in the original table - the audit history isn't important for our purposes.

I am still interested in the answer to the original question for my own edification however.|||

Dan:

Here are a couple of mock-ups of your update:

set nocount on

update destinationTable
set record_value = 'A',
[dateTime] = '12/13/2006 11:00'
--select * from destinationTable

--
-- This works with the particular data but it is really
-- not supported; this is likely to be faster than
-- the second query:
--
/*
UPDATE destinationTable
SET record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join sourceTable st
on dt.record_Id = st.record_id
and st.DML_Type = 'Update'
*/

--
-- This is "safer":
--

update destinationTable
set record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join
( select record_id,
record_value,
[dateTime],
row_number () over
( partition by record_id
order by [dateTime] desc, record_value
) as seq
from sourceTable
where dml_type = 'Update'
) st
on dt.record_id = st.record_id
and st.seq = 1

select * from destinationTable

-- Output:

-- record_id record_value dateTime
-- -- --
-- 1 C 2006-12-13 11:22:00.000
-- 2 C 2006-12-13 11:22:00.000

|||Thanks for the response. Based on your answer, I'm going to assume that the default behavior is undefined and may produce data loss. Now I'll have to read up on partioning :-)