[dba-SQLServer] How does SQL Server do updates

Every month I perform a process where name / address records are extracted from SQL Server, updated 
by a third party app and then only changed data is updated back into SQL Server.  I take changed 
addresses and write them to an 'old address' table and then literally update the existing record 
with the changes.

The address table has an integer PK (which never changes) which came from and is related to another 
table 1 to 1, and that PK is used by itself in a clustered index.  There are other indexes for 
FName, LName Addr ect, my hash fields and so forth.

So, I pull the updated address info into a custom SQL Server DB created on-the-fly for this purpose, 
and then only the changes are updated back into the live database.

The PK and Name fields never change but the address fields do change and other fields which capture 
information about the address changes also change.  The hash fields are updated (in the temp db) and 
written back into live etc.

I am trying to visualize what goes on behind the scenes in SQL Server in the live database.  Most of 
the data fields are varchar(), the hash fields are varbinary(200).  I assume that the data is moved 
around inside of the dbf file, i.e. moved out to new space on the end if it can no longer fit in the 
originally allocated space.  IOW of the town changed from 'Yuma' to 'Los Angeles', something has to 
give.

So as things move around, does SQL Server actually go back and reuse the pieces and parts of empty 
space inside of the file?  Or does it just keep expanding the file and doing everything out at the 
end of the file.


So

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it