[dba-SQLServer] How does SQL Server do updates

Hi Jim --

But JC's records have a clustered index and do not have(?) BLOBs.

http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

So they should be updated in place if there exists enough free space on a record page to adapt to its increased total record length or record's page should be split...

That could be  ROW_OVERFLOW_DATA Allocation Unit where part of records go in the case of updated record length exceed 8KB?

http://msdn.microsoft.com/en-us/library/ms189051(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

The speed of update (insert/update/delete) is important but secondary - the speed of retrieval "is a king"...

Please correct me if I'm wrong and I'm missing something obvious.

Thank you.

-- Shamil


Mon, 8 Oct 2012 07:46:05 -0700 от "Jim Lawrence" <jlawrenc1 at shaw.ca>:
>	
>
>
	
	
>
		
		
			
>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
>
>