dwain.c (7/8/2013)
Excuse me for asking a dumb question here but the OP says the TransactionID is the primary key with presumably a clustered index on it (my assumption).In that case, without an ORDER BY shouldn't the rows be returned in the order established by the clustered INDEX?
Otherwise, how would a QU ever work?
That's a part of the bone of contention that some folks have with the Quirky Update. The QU depends on the use of the Clustered Index AND that it's used in an "ORDERED" fashion. That's the reason for all the rules such a MAXDOP 1 and referencing the lead column of the CI, etc. Paul White came up with a method (referenced at the beginning of the "latest" article) that uses a CTE with a RowNumber and a counter in the outer query that must match or it gives you an error. Oddly enough, the method seems to guarantee that if the QU is setup correctly to run to begin with, it'll never error.
As for relying on the CU for a "natural" sort of other things goes, it's not guaranteed especially if there's another unique index on the table. Other than some of the trickery in the rules of a QU, the only way to guarantee order in a SELECT is to use an ORDER BY. If SQL Server decides to use the CI in an "ORDERED" mode, it'll ignore the sort... it won't even include it in the execution plan.
--Jeff Moden
Change is inevitable... Change for the better is not.