[AccessD] How does SQL Server do updates

jwcolby jwcolby at colbyconsulting.com
Mon Oct 8 10:18:13 CDT 2012


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
>



More information about the AccessD mailing list