[dba-SQLServer] How does SQL Server do updates

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