如何高效向大型varchar(max)列追加内容？SQL Server更新性能咨询

假设有如下结构的SQL Server表：

CREATE TABLE Product (
    Id int PRIMARY KEY CLUSTERED,
    InvoicesStr varchar(max)
)

表中InvoicesStr字段存储对应商品关联的发票ID拼接字符串，该设计不符合数据库设计规范，仅用于本次问题演示。
表内数据示例如下：

Product

Id  | InvoicesStr
----|-------------------------------------
1   | 4,5,6,7,34,6,78,967,3,534,
2   | 454,767,344,567,89676,4435,3,434,

当商品销量达到数百万级时，InvoicesStr存储的字符串长度会极高，极端场景下单行该字段的存储内容可达1GB。

待评估性能的更新语句如下：

UPDATE Product
SET InvoicesStr = InvoicesStr + '584,'
WHERE Id = 100

核心疑问：

• 该更新语句的执行性能是否与InvoicesStr字段原有内容的大小相关？
• SQL Server是否能智能识别字符串追加操作，仅写入新增内容，无需重写整个字段？

该更新语句的性能与InvoicesStr原有内容大小呈强线性相关，示例中的普通字符串拼接写法完全不会触发部分写入优化，SQL Server会重写整个字段的全部内容。
具体逻辑如下：

• SQL Server的查询优化器和存储引擎不会解析InvoicesStr + '584,'这个表达式的语义，无法识别出这是「在字符串末尾追加内容」的操作，只会先计算表达式的最终完整结果，再将整个结果完整写回对应的数据页。
• 当varchar(max)字段内容超过行内存储的8KB阈值后，数据会被存储在独立的LOB（大对象）数据页中，行内仅保留16字节的指向LOB结构的指针。执行上述更新时，SQL Server需要先把整个字段的原有内容全部读取到内存完成拼接，再把拼接后的完整新值全部写入新的LOB空间，旧的LOB数据会被标记为待回收，整个过程的IO、内存、事务日志开销都会随原有字段的大小线性增长。如果字段内容达到1GB级别，单次更新就可能产生数GB的事务日志，执行耗时会非常长。
• SQL Server确实为大值类型提供了部分更新的能力，但必须显式调用大值类型的.WRITE()方法、明确指定追加参数才会触发。如果要实现高效末尾追加，需要把语句改写为如下形式：

  UPDATE Product
  SET InvoicesStr.WRITE('584,', NULL, 0)
  WHERE Id = 100
  

  上述写法中第二个参数传NULL代表从原有字符串的末尾位置开始写入，第三个参数0代表不删除原有内容、直接插入新值，这种场景下SQL Server才会仅写入新增的片段，最小化IO和日志开销，性能几乎和原有字段大小无关。

内容的提问来源于stack exchange，提问作者mehrandvd