August 21, 2006 at 9:59 am
I've heard that updates should be done in the order of most recent records first, then working backward. I have not tried this before in my update statements, I've just been letting SQL go in its usual order from lowest/oldest ID to new. Is this sound advice? What is the advantage? The typical scripts I do are batched updates on databases with several years' worth of records in each table. I can see the advantage if the script is running at varying intervals until the update is complete, so the current data gets done first. But for a script that runs once-only to do the full update?
August 22, 2006 at 3:21 am
You normally can't decide which in which order an UPDATE is executed, but there are workarounds such as UPDATING the table in chunks.
N 56°04'39.16"
E 12°55'05.25"
August 22, 2006 at 4:27 am
Can you explain what do you mean by "updates should be done in the order of most recent records first, then working backward"? Do you mean order of columns that are updated?
Zubeyir
August 22, 2006 at 10:16 am
Order of rows updated. And I understand SQL normally works oldest to newest rows. I'm curious why I've been told to do it opposite and can't find a good reason for it.
August 22, 2006 at 10:35 am
>>And I understand SQL normally works oldest to newest rows.
Where did you get that understanding ? There is no inherent order in which SQL updates under the hood. It is irrelevant. That's why you have a T-SQL execution engine between you and the data, so you don't have to consider the physical implementation of any SQL operation.
Maybe you're getting confused with clustered indexes and physical ordering of data pages according to columns in the clustered index ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply