title: SQL Server: SQL request returns after writing to t-log or after b-tree is updated
body: <p>For mutating SQL statements (insert, update, delete), when does SQL returns response to client? </p>
  
  <p>Is this </p>
  
  <ol>
  <li>after the change has been written to the transaction log but b-tree hasn't been updated, OR</li>
  <li>after the change has been written to the transaction log and b-tree index has been updated</li>
  </ol>
  
  <p>I am confused because in order for seek operations to work, the b-tree index has to be up-to-date, so I would imagine #2 is correct.</p>
  
  <p>But transaction log is also used to recover from crash. If SQL crashes in the middle of a mutating request, the client never receives success code anyway and the client can retry.</p>
  
  <p>So in what scenario does the t-log provides disaster recovery, assuming there is no replication involved?</p>