If a row in a table is updated, indexes on the table also must be updated. For small updates, such as OLTP operations, it is appropriate to update the indexes row-by-row as you update each row of the base table.
For large updates, such as a data warehouse refresh, row-by-row updates can be inefficient, resulting in a high volume of random I/O to the index records. A better approach is to delay updating the indexes until all the base tables are updated, then presort the changes per index and simultaneously merge all the changes into the index. This assures that each index leaf page is touched once at most and that SQL Server traverses each B-tree index sequentially.
The query optimizer takes this approach if it is the least expensive approach. The benefit is that large data warehouse refreshes can be accomplished more efficiently.
The query optimizer also plans the join operations required to enforce referential integrity constraints, making a cost-based choice among (index) nested loops join, merge join, and hash join.