[AccessD] How does SQL Server do updates

Darn it,

I've just posted the same sort of info and the same link on rhe "correct" list.

Hey JC, please stop cross-posting.  I filter my mail into topic folders with a different folder for 
each list and having the same conversation in  different places is a PITA,

-- 
Stuart

On 8 Oct 2012 at 10:43, Jim Dettman wrote:

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>