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