From version 7.0 onwards, SQL Server will update a value in a row directly, without having to delete and re-insert the row. The exception to this is when the value being updated is part of a clustered index. If this is the case then SQL Server will perform a 'deferred update' which consists of a delete and insert (perhaps even on a different page) together with the required transaction log and index page changes. This process can be quite resource intensive - the moral of this story is to avoid the inclusion of frequently updated columns in clustered indexes.