November 29, 2012 at 4:58 am
Hi guys
I have a script that does the following;
updates a date field from 31/12/2099 to today
Bulk Loads new records and the default for the date field is 31/12/2099. The data in the bulk load is not sorted; the bulk load uses a .fmt file as there are fields in the table which are not in the import file.
Within each load there is a record ID which is unique to the load, but not unique to the table (ID and date would be unique even though this is not set as a constraint)
The date field is used in all indexes (non clustered, non unique)
The change in date should not affect the index paging as the index pointer would already be at the end of the index (date changes from 2099 to current date, all prior records have earlier dates)
My question is , when the update script runs, does SQL update the indexes first, then update the physical records, or does it update the records first, then the indexes or does it update each index entry AS is updates the data.
Again, with the bulk load, does it write the data then the indexes or does it write each index pointer as the records are inserted into the table
November 29, 2012 at 6:00 am
Pretty sure it updates the physical storage and then the indexes... except where the index is a clustered index, which you didn't mention. Then, the index is the physical storage so changes to the values will be reflected immediately.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2012 at 6:22 am
aaron.reese (11/29/2012)
My question is , when the update script runs, does SQL update the indexes first, then update the physical records, or does it update the records first, then the indexes or does it update each index entry AS is updates the data.
Depends on the execution plan, but since both will occur within the transaction, before any locks are released it doesn't make that much of a difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply