I recently found to my surprise that a query such as
update mytab set displayorder = displayorder+1 where displayorder > 50;
worked, even though displayorder had a unique key index and all of the values over 50 were contiguous.
update mytab set displayorder = displayorder-1 where displayorder > 50;
worked, although (of course, since there was also a 50) another such decrementing update violated the constraint and failed.
I was surprised because there isn't anything to indicate that the rows would be processed in an order that wouldn't violate the constraint.
When the update processes the record with a value of 55 and there's already a record with a value of 56, updating record 55's value to 56 creates a temporary duplication (at least until record 56 is processed).
But it works.
My question is, can you rely on this? I'm using SQL Server 2008 R2, but I've no idea if other SQL implementations would have this behavior.
Does this work because constraints and indexes only enforced on a commit?
If, so, I suppose that when a statement is run outside of a specific transaction, the SQL engine wraps it in a mini-transaction of its own?