SQL Server 2012 - 分离2个Varchar（最大）列到单独的表？ [英] SQL Server 2012 - Separate 2 Varchar(max) columns to separate table?

问题描述

如果SQL Server 2012表有2 varchar（max）列，这些列很少使用并导致表快速增长，那么分割为单独的表？

两个 varchar（max）列中的数据用于存储来自API的原始JSON响应。 p>

如果存储在单独的表中，则行将被截断为只包括前6个月，从而减少第一个表的表大小。

解决方案

如果存储在单独的表中，则行将被截断为只包括前6个月，从而减少第一个表的表大小。

行必须被删除，而不是截断，然后BLOB空间必须通过运行 ALTER INDEX来回收。 .. REORGANIZE WITH（LOB_COMPACTION = ON）

如果您将blob存储在原始表中，行到 SET blob = NULL 然后用回收空格ALTER INDEX ... REORGANIZE WITH（LOB_COMPACTION = ON）

所以，当它细化到细节，你没有实现很多使用拆分表，imho。因此，我坚持我之前的建议，来自 SQL Server varbinary（max）和varchar（max）数据在一个单独的表中：我没有看到分割的好处，但我看到麻烦从分裂之间保持行1：1。

您可能有一个case如果拆分和分区'blobs'表。然后，你可以通过切换out旧的分区并用空的替换它们，然后丢弃已切换出的数据，从而非常有效地释放旧空间。 是需要考虑的事情。当然，你的代码必须足够聪明，当它加入两个分裂，以考虑blob可能会消失（例如使用OUTER JOIN）。

If a SQL Server 2012 table has 2 varchar(max) columns that are rarely used and causing a table to grow rapidly, does it make sense to split into a separate table?

Data in the two varchar(max) columns is used to store raw JSON responses from an API.

If stored in a separate table, rows would be truncated to only include previous 6 months, reducing table size of first table.

解决方案

If stored in a separate table, rows would be truncated to only include previous 6 months, reducing table size of first table.

The rows would have to be deleted, not truncated, and then the BLOB space would have to be reclaimed by running ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON)

If you'd store instead the blobs in the original table, you would have to update the rows to SET blob = NULL and then reclaim the space with ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON)

So when it boils down to the details, you aren't achieving much using a split table, imho. So I stick to my earlier advice from SQL Server varbinary(max) and varchar(max) data in a separate table: I see no benefits in split, but I see trouble from having to maintain the rows consistent 1:1 between the splits.

You may have a case if you split and partition the 'blobs' table. Then you could, indeed, deallocate very efficiently the old space by switching 'out' the old partition and replacing them with an empty one, and then dropping the switched out data. That is something to consider. Of course, you code would have to be smart enough when it joins the two 'splits' to consider that the blobs may be gone (ee. use OUTER JOIN).

这篇关于SQL Server 2012 - 分离2个Varchar（最大）列到单独的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！