Asger Blond
ab-mi at post3.tele.dk
Tue Oct 9 18:21:19 CDT 2012
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