What happen when we Modify data in the sql server

 SQL Server does not read or modify data rows directly on the disk. Every time we access data, SQL Server reads it into memory. Let’s see what happen when we are modify any records. Data is present as below

Suppose we want to update the data row from the page (1:28992).First it will bring it into the Memory. This memory cache is called a buffer pool that caches some of the data pages. SQL Server needs to read the data page from the disk and store it into the buffer pool. When the page is in memory or buffer pool, SQL Server updates the data row. This process includes two different steps. First, SQL Server generates a new transaction log record and synchronously writes it to the transaction log file. Next, it modifies the data row and marks the data page as modified (dirty). 

Even though the new version of the data row is not yet saved in the data file, the transaction log record Contains enough information to reconstruct (redo) the change if needed. Finally, at some point, SQL Server asynchronously saves the dirty data pages into the data file and a special log record into the transaction log. This process is called a checkpoint. 

The insert process works in a similar manner. SQL Server reads the data page where the new data row Needs to be inserted into the buffer pool, or it allocates a new extent/page if needed? After that, SQL Server synchronously saves the transaction log record, inserts a row into the page, and asynchronously saves the data page to the disk. The same process transpires with deletions. As already mentioned, when you delete a row, SQL Server does not physically remove the row from the page. Rather, it flags deleted rows as ghosted (deleted) in the status bits. This speeds up deletion and allows SQL Server to undo it quickly if necessary.