在SQL Server中存储可变数量列的高效方案咨询（C#百万级写入场景）

嗨，这个动态 schema 下的大规模数据存储场景太常见了——我之前帮好几个团队处理过类似的需求，结合SQL Server的特性和C#的实现，给你梳理几个最高效的方案：

这是SQL Server原生支持的动态列方案，特别适合自定义列仅针对特定用户组、其他组数据为NULL的场景。稀疏列对NULL值不占用存储空间，列集则把所有稀疏列打包成一个XML类型的虚拟列，方便C#统一读写动态列。

实现要点： #

• 当需要新增自定义列时，用C#动态执行DDL语句创建稀疏列：

  // 示例：给特定用户组新增自定义列
  string addColumnSql = "ALTER TABLE Records ADD UserGroupX_CustomCol1 NVARCHAR(100) SPARSE";
  using (var cmd = new SqlCommand(addColumnSql, conn))
  {
      cmd.ExecuteNonQuery();
  }
  

• 读写列集时，可以直接操作XML，或者在C#里用ExpandoObject动态映射属性，再序列化为XML写入列集。
• 查询时可以直接用自定义列名，性能和普通列几乎无差别。

优缺点： #

✅ 原生支持，性能接近普通表，查询便捷
❌ 需要动态创建列，列数上限为30000（稀疏列），如果自定义列极多会受限

这是动态列场景的经典方案，适合完全无法预估自定义列数量、部分自定义列对应数百万条记录的情况。核心是拆分两张表：

• 主表：存储标准列（如Records(Id, StandardCol1, StandardCol2, ...)）
• 属性表：存储自定义键值对（如RecordAttributes(RecordId, AttributeName, ValueInt, ValueString, ValueDateTime)）

实现要点： #

• 批量写入用SqlBulkCopy，这是SQL Server批量插入的最优方式，能轻松处理数百万条属性数据：

  // 示例：批量插入属性数据
  var attributeTable = new DataTable();
  attributeTable.Columns.Add("RecordId", typeof(int));
  attributeTable.Columns.Add("AttributeName", typeof(string));
  attributeTable.Columns.Add("ValueInt", typeof(int));
  
  // 填充数百万条数据到attributeTable...
  
  using (var bulkCopy = new SqlBulkCopy(conn))
  {
      bulkCopy.DestinationTableName = "RecordAttributes";
      bulkCopy.WriteToServer(attributeTable);
  }
  

• 给属性表加(RecordId, AttributeName)的复合索引，常用属性可以单独加列存储索引，大幅提升聚合查询性能。

优缺点： #

✅ 完全动态，无需提前创建列，支持无限多自定义属性
❌ 多属性查询需要JOIN，复杂查询性能略低于普通表，需做好索引优化

如果自定义列的查询频率不高，或者更看重开发效率，直接在主表加一个NVARCHAR(MAX)（存JSON）或XML类型的列，把所有自定义键值对序列化后存储是最简单的方案。

实现要点： #

• 在C#里用System.Text.Json或Newtonsoft.Json把动态属性序列化为JSON字符串：

  var customProps = new Dictionary<string, object>
  {
      {"UserGroupY_CustomColA", "TestValue"},
      {"UserGroupY_CustomColB", 12345}
  };
  string customJson = JsonSerializer.Serialize(customProps);
  

• 对于频繁查询的自定义属性，可以创建计算列并加索引：

  ALTER TABLE Records ADD UserGroupY_CustomColA AS JSON_VALUE(CustomData, '$.UserGroupY_CustomColA') PERSISTED
  CREATE INDEX IX_Records_CustomColA ON Records(UserGroupY_CustomColA)
  

• 批量写入同样用SqlBulkCopy，性能非常可观。

优缺点： #

✅ 开发成本极低，无需额外表，完全动态
❌ 复杂查询（过滤、排序）性能略差，需依赖计算列索引优化

• 如果自定义列数量不多、且每个用户组的列相对固定（只是提前未知）：优先选稀疏列+列集，性能最优。
• 如果自定义列数量极多、完全无法预估：选EAV模式+列存储索引，扩展性最强。
• 如果开发周期短、查询自定义列频率不高：选JSON列，实现最简单。
• 如果是高并发写入场景：任何方案都可以结合内存优化表，写入性能比磁盘表提升数倍。

1. 批量操作必用SqlBulkCopy，比循环INSERT快10~100倍。
2. 关闭自动提交，用批量事务（比如每10万条提交一次），减少日志开销。
3. 对于JSON/EAV存储，尽量匹配数据类型（比如数字存int而非字符串），节省空间并提升查询性能。
4. 超大规模数据（数千万条+）可以用分区表，按用户组或时间分区，提升查询和维护效率。

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