[AccessD] How does SQL Server do updates

Thanks for that Jim.

John W. Colby
Colby Consulting

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

On 10/8/2012 10:43 AM, 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
>