HowardW (3/4/2011)
It's guaranteed to delete the nth row within that delete statement, but as far as I'm aware, you could run the select (which will generate one plan) and assume that the delete statement will delete the same rows. However, the delete statement could reasonably generate a different plan and delete different arbitrary rows than your original select statement did. Unless I'm misunderstanding how the optimiser works in this case and it doesn't generate a plan based on the overall query and will always generate the plan based on the sub query alone. Hopefully the above makes sense (even if it's wrong!) 🙂
I see, so you were saying that running the SELECT first (and noting the row it selected) and then expecting the DELETE (with the same SELECT statement as a subquery) to delete the same row would be unsound. I agree with that of course.
I thought you were saying that the DELETE might not delete the row identified by the SELECT (when run as part of the DELETE statement). My apologies - I think I did misread what you were getting at.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi