[AccessD] How does SQL Server do updates

Jim Dettman jimdettman at verizon.net
Wed Oct 10 12:19:28 CDT 2012


 
It should be pointed out that on this:

"If your update can't happen in place because you're updating clustering
keys, the update will occur as a delete followed by an insert." (p. 309)"

 Is a requirement only because of clustered indexes, not because that's the
way it needs to be.

 A clustered index by definition means that the pages and records are
physically in the order of the index, which is why the record needs to be
moved, but only if a field is defined as part of the index changes.  If none
do, then the record won't move.

 As I said previously, inserting a record is one of the most costly tasks
that a RDBMS can do.  Page space needs to be found, Page File Management
records (if any) need to be updated), indexes updated, DB might need to
expand, etc.

 From a performance stand point, one of the last things a RDBMs wants to do
is move a record.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Tuesday, October 09, 2012 07:21 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] How does SQL Server do updates

To be more precise ... I quote this from Kalen Delaney's book Inside
Microsoft SQL Server 2005: The Storage Engine:

"In SQL Server 2005, updating a row in place is the rule rather than the
exception. This means that the row stays in exactly the same location on the
same page and only the bytes affected are changed. In addition, the log will
contain a single record for each such updated row unless the table has an
update trigger on it or is marked for replication. In these cases, the
update still happens in place, but the log will contain a delete record
followed by an insert record.
(.....)
If your update can't happen in place because you're updating clustering
keys, the update will occur as a delete followed by an insert." (p. 309)

So I was obviously wrong stating that a rollback is enabled by the log
having both the original and the new record. In the first case mentioned by
Delaney the log doesn't hold the original record. It may be more precise to
say that a rollback is enabled by the fact that a transaction first goes to
the cache before written to the log and that the rollback is accomplished in
the cache before any physical flushing of data to the log file or the data
file happens.

Asger



-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Asger Blond
Sendt: 10. oktober 2012 00:13
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] How does SQL Server do updates

Arthur,

You need to distinguish what's going on in the transaction log and on the
data pages. The transaction log holds both the original and the changed
records to enable a rollback, but that doesn't mean that the change on the
data pages can't be in-situ. In fact I think it is: a delete-insert
operation on the data pages seems ineffective to me.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Arthur Fuller
Sendt: 9. oktober 2012 23:05
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] How does SQL Server do updates

Precisely my point, Jim. In-situ updates by definition destroy data and
prevent rollbacks; hence the delete-then-insert scenario, without which a
rollback would be impossible. Thus, UPDATE is a glorified term for DELETE
then INSERT. No other way to make it all work. Nuff said,,, or perhaps for
the sake of performance you would prefer to sacrifice RollBack.

A.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list