SQL Server 2008：禁用一个特定表分区上的索引 [英] SQL Server 2008: Disable index on one particular table partition

问题描述

我正在使用SQL Server 2008中的一个大表（〜100.000.000行）。通常，我需要添加和删除这个表中的〜30.000.000行的批次。目前，在将大批量加载到表中之前，我禁用索引，我插入数据，然后重建索引。我已经测量出这是最快的方法。

I am working with a big table (~100.000.000 rows) in SQL Server 2008. Frequently, I need to add and remove batches of ~30.000.000 rows to and from this table. Currently, before loading a large batch into the table, I disable indexes, I insert the data, then I rebuild the index. I have measured this to be the fastest approach.

最近，我正在考虑在这个表上实现表分区以提高速度。我将按照我的批次分区表。

Since recently, I am considering implementing table partitioning on this table to increase speed. I will partition the table according to my batches.

我的问题是，是否可以禁用一个特定分区的索引，并在再次启用数据之前加载数据？在这种情况下，我的桌子的其余部分不一定要经历一个完整的索引重建，而且我的加载速度可以更快？

My question, will it be possible to disable the index of one particular partition, and load the data into that one before enabling it again? In that case, the rest of my table will not have to suffer a complete index rebuild, and my loading can be even faster?

推荐答案

p>索引通常在分区方案中。对于您正在谈论的场景，您实际上可以使用批处理（相同的结构，不同的名称）加载新表，然后使用SWITCH命令将此表作为新分区添加到现有表中。

Indexes are typically on the Partition Scheme. For the scenario you are talking about you can actually load up a new table with the batch (identical structure, different name) and then use the SWITCH command to add this table as a new partition into your existing table.

我已经包含了用于执行此操作的代码，您将需要根据您的表名进行修改：

I have included code that I use to perform this, you will need to modify it based on your table names:

DECLARE @importPart int
DECLARE @hourlyPart int

SET @importPart = 2 -- always, so long as the Import table is only made up of 1 partition

-- get the Hourly partition
SELECT 
    @hourlyPart = MAX(V.boundary_id) + 1
FROM 
    sys.partition_range_values V
JOIN    sys.partition_functions F
    ON  V.function_id = F.function_id
    AND F.name = 'pfHourly'

ALTER TABLE Import
SWITCH PARTITION @importPart
TO Hourly PARTITION @hourlyPart;

这篇关于SQL Server 2008：禁用一个特定表分区上的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！