sql-server - 更新具有相同值的表会增加事务日志文件的大小吗？

Question

还是事务日志仅包含差异？

这两个脚本之间的日志文件是否存在差异:

DECLARE @f1 INT ,
    @f2 INT 

CREATE TABLE #t ( int F1, int f2 )
UPDATE  #t
SET     f1 = ISNULL(@f1, f1) ,
        f2 = ISNULL(@f2, f2)

--or
IF @f1 IS NOT NULL
    UPDATE  #t
    SET     f1 = ISNULL(@f1, f1)  

IF @f2 IS NOT NULL
    UPDATE  #t
    SET     f2 = ISNULL(@f2, f2)

Answer 1

仅当更新是集群 key 的一部分时。

看看这个 article标题为“非更新更新的影响”，讨论了这个问题。

Summary

SQL Server contains a number of optimisations to avoid unnecessary logging or page flushing when processing an UPDATE operation that will not result in any change to the persistent database.

• Non-updating updates to a clustered table generally avoid extra logging and page flushing, unless a column that forms (part of) the cluster key is affected by the update operation.
• If any part of the cluster key is ‘updated’ to the same value, the operation is logged as if data had changed, and the affected pages are marked as dirty in the buffer pool. This is a consequence of the conversion of the UPDATE to a delete-then-insert operation.
• Heap tables behave the same as clustered tables, except they do not have a cluster key to cause any extra logging or page flushing. This remains the case even where a non-clustered primary key exists on the heap. Non-updating updates to a heap therefore generally avoid the extra logging and flushing (but see below).
• Both heaps and clustered tables will suffer the extra logging and flushing for any row where a LOB column containing more than 8000 bytes of data is updated to the same value using any syntax other than ‘SET column_name = column_name’.
  • Simply enabling either type of row versioning isolation level on a database always causes the extra logging and flushing. This occurs regardless of the isolation level in effect for the update transaction.