SQL Server - 从VLT（非常大的表）中删除列

Question

请问如何最好地实现以下需求：

要求：从大小接近400 GB的VLT中删除5列。

我们尝试执行此操作时，会在生产环境中遇到空间问题和SSMS的超时错误。

我们尝试将数据插入到任何临时表中（关闭identity），但是当我们尝试开启identity后，我们遇到了超时错误，因为我们插入了近十亿行数据。

我们是否应该通过POWERSHELL执行这些操作而不是通过SSMS执行？

限制：生产环境中的空间有限，由于这些操作，tempdb正在快速增长。

请建议如何最好地从VLT中删除列。

谢谢！

- Conrad Jagger

---

无论哪种方式，您都需要更多的空间——如果没有别的事情，您将需要空间来容纳在后台创建的临时表，以保存修改/新结构。 - Marc B

好的问题，但您可能会在http://dba.stackexchange.com获得更好的反响。 - Paul Sasik

这些列中有什么（类型、值的种类），需要删除吗？ - Grzegorz Gierlik

要删除的列类型主要是varchar(250)。我们将数据导入了一个新的临时表，其中有5个字段，原来有10个（其中5个被删除）。但是，如果我们在这张现在只有300GB的表上尝试使用IDENTITY ON，我们会遇到超时错误。 - Conrad Jagger

Answer 1

我会采取之前提到的方法之一，但会进行一些关键修改。假设您在使用SQL Server 2008，请按照以下步骤操作：

1. 创建一个只包含要保留列的现有非常大表的零长度副本：

select top 0 {{column subset}} into tbl_tableB from tableA

确保同时将任何索引、约束等复制到新表中。标识列将由SELECT...INTO语句适当处理。

2. 重命名原始表；我们将在下一步中用视图替换它。

exec sys.sp_rename @objname = 'tableA', @newname = 'tbl_tableA'

创建一个视图，使用原始表名和UNION ALL。

create view tableA
as
select {{column subset}} from tbl_tableA
union all
select {{column subset}} from tbl_tableB

这将保持与查询数据的应用程序的某种兼容性。通过视图触发器处理INSERTs、UPDATEs和DELETEs。使用UNION ALL可以防止在tempdb中出现压力，因为不会进行排序（与普通的UNION相比），而且我们永远不会同时存在超过一份行的副本。

3. 使用DELETE与OUTPUT子句结合使用，从原始表中批量删除数据并同时将其插入新表：

BEGIN TRAN
DELETE TOP (1000) /* or whatever batch size you want */
FROM
    tbl_tableA
OUTPUT (
    DELETED.{{column subset}} /* have to list each column here prefixed by DELETED. */
)
INTO
    tbl_tableB (
        {{column subset}} /* again list each column here */
    )
/* Check for errors */
/* COMMIT or ROLLBACK */
/* rinse and repeat [n] times */

4. 在完成DELETEs/INSERTs之后，删除视图，删除原始表，重命名新表：

drop view tableA
drop table tbl_tableA
exec sys.sp_rename @objname = 'tbl_tableB', @newname = 'tableA'

这种方法的最大优点是，DELETE和INSERT在同一事务中同时发生，这意味着数据始终处于一致的状态。您可以通过更改TOP子句来增加批处理的大小，从而更好地控制事务日志的使用和阻塞。我已经在具有标识列和没有标识列的表上测试了这种方法，效果非常好。对于非常大的表，运行时间可能需要几个小时到几天不等，但它将以所需的结果完成。

- Mitch Schroeter

Answer 2

ALTER TABLE ... DROP 是一个仅涉及元数据的操作，只要能够在表上获得排他锁定（这意味着使用表的所有查询必须被耗尽才能完成），则该操作几乎瞬间完成。但是删除列并不会物理删除它们，请参阅 SQL Server表列底层原理。

下一步是必要时删除实际列。我强调“必要时”，因为根据列类型，这可能不值得努力。对于可变长度列，您可以通过运行 DBCC CLEANTABLE 来回收空间。但是，如果您已从非压缩表（没有页面或行压缩）中删除了固定大小的列，则恢复空间的唯一方法是重建表（堆或聚集索引）。如果表进行了分区，则可以尝试逐个离线重建分区（ALTER TABLE ... REBUILD PARTITION = N）。如果没有，最好采用在线重建，前提是没有 MAX 类型的列（此限制在SQL Server 2012中解除）。在线重建会生成大量日志（至少是数据大小的1.5倍），但是它在内部提交，因此日志备份维护可以回收空间，并且您不会遭受600GB的日志增长。如果无法进行在线重建且表未分区，则我首先会重新审查清理删除列的决定。

如果强制进行列清理，而又没有在线选项可用的话，那么你真的会面临巨大的痛苦。这样一个庞大的操作需要几天的准备、测试，而且一点都不琐碎。你需要创建一个具有所需结构的新表，以分批次将数据传输到其中，并设置一些机制来跟踪已复制数据的更改情况，然后应用于副本中。当所有数据被复制并应用自复制开始以来发生的更改时，就可以使用sp_rename切换旧表和新表。总的来说，如果你能使用现成的在线选项，那么你会好得多。

- Remus Rusanu

Answer 3

我会建议使用另一个表和批处理的组合。 1 - 创建一个新的表结构，使用与旧表相同的聚集索引键。 2 - 创建一个视图，将旧表和新表联接起来，以便需要时可以对两个表都进行连续访问。为了限制生产中的问题，您可以将其命名为与原始表相同，并将表重命名为_Old或其他名称。在视图中仅包括所需字段，而不是要删除的字段。 3 - 在事务内：

• 向新表插入若干行（比如每次1m行）
• 从旧表中删除，使用新表连接

此方法具有低日志增长（因为您正在批处理），低数据库增长（因为额外行数永远不会超过您的批处理大小），并且是增量式的，因此如果速度变慢，则可以停止操作。

坏消息是，由于您正在删除记录，因此一旦开始，您基本上就已经致力于该过程。根据需要排序的数量，您还可能会遇到来自UNION视图的tempdb压力。

- JNK

---

当然，在开始任何此类操作之前，请确保您拥有良好的备份策略。 - HLGEM

@HLGEM - 我乐观地认为，对于这么大的表格，他们已经基本上想好了备份方案。 - JNK

Answer 4

我会考虑创建一个新的分区表，使用你想要的模式，并将数据插入到切换表中，然后将这些表切换到新表中。

如果你对分区表和索引不是很熟悉，我强烈推荐 Kimberly Tripp 的这篇优秀白皮书。

在将数据插入到切换表中时，你可以通过以下方式强制进行最小化日志记录：

1. 你的转换表必须为空。
2. 你的数据库应该处于简单恢复模式

3. 你需要像这样使用跟踪标志610：

   DBCC TRACEON(610)

4. 你需要在表上使用tablock提示，像这样：

   INSERT newtable WITH (TABLOCK)
   SELECT col1, col2, col3, col4 
   FROM oldtable
   WHERE col1 BETWEEN min and max
   

5. 转换表需要有一个聚集索引

祝你好运。希望这对你有所帮助。我在SQL Server中使用VLDBs，发现分区在加载和移动数据方面非常有价值。

- capnsue

---

这是一个非常大的变化，只为了一次数据结构的更改...这也很危险，因为他需要非常仔细地选择他的分区键等，因为为了热交换分区，他所有的索引都需要进行分区对齐。 - JNK

Answer 5

我没有处理过那么大的表格，但如果是我，我会尝试将数据（仅保留需要的列）BCP到操作系统文件中，删除表格，然后将数据BCP回新表格中，只保留所需的列。当然，这假定您有能力在进行此维护时将服务器脱机（并且在开始之前备份良好）。

- E.J. Brennan