Jim Lawrence
accessd at shaw.ca
Tue Oct 9 12:13:23 CDT 2012
And that is why most big active databases can run roll backs and now even flash backs, where a database can be rolled back to a certain position in time...even sometimes days, depending on how the setup was done. If the changes, deletions and additions were not just being added to the database; these roll backs could not take place. It is always fastest to just add to the DB and worry about the cleanup later. Aside: Warehouse type databases work much slower but then there are no expectations of performance or changes. Jim PS That is why the reduced map databases are so efficient and fast. They just add records and queue cleanup and processing for later when the DB is not under load and/or just distribute the burden to other machines. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Tuesday, October 09, 2012 5:11 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] How does SQL Server do updates I believe this is incorrect. SQL Server only needs to move a record if it becomes larger, or if a field that is changed is part of a clustered index (in that case, the record will most likely be moved to a new page). I can't imagine any RDBMS today would take the inefficient route of updating a record by always deleting the original and then inserting it, even within the same page. It may appear that way, but I doubt that's what actually happens. Inserting a record for an RDBMS is always an expensive operation. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms Sent: Monday, October 08, 2012 08:26 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] How does SQL Server do updates One question: WHY ? > SQL Server doesn't ever physically update a row, but instead does an > insert and a delete. This can be verified by creating a trigger For Update, > inside which you'll find the tables #inserted and #deleted. In #inserted > you'll find some of the original row's columns, with new values for the > columns you changed. Then SQL deletes the row(s) in #deleted, and inserts the > "replacement" rows in #inserted. -- 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