Logging and Transaction
SQL Server ensures that any change to the data is ACID-compliant, i.e. it uses transactions to ensure that the database will always revert to a known consistent state on failure. Each transaction may consist of multiple SQL statements all of which will only make a permanent change to the database if the last statement in the transaction (a COMMIT statement) completes successfully. If the COMMIT successfully completes the transaction is safely on disk.
SQL Server implements transactions using a write-ahead log.
Any changes made to any page will update the in-memory cache of the page, simultaneously all the operations...
