[AccessD] How does SQL Server do updates

John,

<<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.>>

  SQL Server like many other RDBMS manages things around a fixed page size,
with a page header, some records, free space, etc.

  Tons of info on the net on this.  Here's one:

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-o
f-a-page.aspx

<<
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.
>>

 Reuses space on a page, but there's no mechanisms in place to manage pages.
It's optimized for speed, so if it needs a new page, it goes out and grabs a
new one rather then trying to find an existing page with space.  That's a
short explanation, but the jist of it (SQL server actually tracks extents,
which are made up of pages and not actual pages).

Jim.


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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com