[dba-SQLServer] How does SQL Server do updates

I believe MS SQL just sticks the records on the end of the table. 

This is of course done for speed. It is much easier to just add a record at
the end of the table than try to insert it in the middle, replacing a
deleted record's position. (This is of course how old FoxPro and MS Access
works and I am assuming MS SQL works basically, just the same...)

Jim  

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, October 08, 2012 3:37 AM
To: Sqlserver-Dba; Access Developers discussion and problem solving
Subject: [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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com