[dba-SQLServer] How does SQL Server do updates

See 
http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.
aspx


On 9 Oct 2012 at 7:13, Stuart McLachlan wrote:

> It also depends on the field type.
> 
> To oversimplify, if it is a fixed length type, the field will be part of the record - if it is a vrariable 
> length field, the main record will only hold a pointer to the data and the contents will be stored 
> somwhere else in a paged data area.   SQL Server manages the pages adding them as 
> necessary and reusing them when they are emptied so it is not just a case of bloating the db 
> every time you edit a varchar.
> 
> -- 
> Stuart  
> 
> 
> On 8 Oct 2012 at 7:46, Jim Lawrence wrote:
> 
> > 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
> > 
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> > 
> > 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>