在临时表上创建主键 - 何时？ [英] Creating a Primary Key on a temp table - When?

问题描述

我有一个处理大量数据的存储过程。我将数据插入到临时表中。整个事件流程类似于

I have a stored procedure that is working with a large amount of data. I have that data being inserted in to a temp table. The overall flow of events is something like

CREATE #TempTable (
    Col1    NUMERIC(18,0) NOT NULL,    --This will not be an identity column.
    ,Col2   INT NOT NULL,
    ,Col3   BIGINT,

    ,Col4   VARCHAR(25) NOT NULL,
    --Etc...

    --
    --Create primary key here?
)


INSERT INTO #TempTable
SELECT ...
FROM MyTable
WHERE ...

INSERT INTO #TempTable
SELECT ...
FROM MyTable2
WHERE ...

--
-- ...or create primary key here?

我的问题是何时是在#TempTable表上创建主键的最佳时间？我认为我应该在插入所有数据后创建主键约束/索引，因为索引需要在创建主键信息时重新组织。但我意识到我的强调假设可能是错误的......

My question is when is the best time to create a primary key on my #TempTable table? I theorized that I should create the primary key constraint/index after I insert all the data because the index needs to be reorganized as the primary key info is being created. But I realized that my underlining assumption might be wrong...

如果相关，我使用的数据类型是真实的。在 #TempTable 表中， Col1 和 Col4 将是编造我的主键。

In case it is relevant, the data types I used are real. In the #TempTable table, Col1 and Col4 will be making up my primary key.

更新：就我而言，我正在复制源表的主键。我知道构成我的主键的字段将始终是唯一的。如果我在最后添加主键，我不关心失败的alter table。

Update: In my case, I'm duplicating the primary key of the source tables. I know that the fields that will make up my primary key will always be unique. I have no concern about a failed alter table if I add the primary key at the end.

虽然，除此之外，我的问题仍然存在，假设两者都会成功会更快？

Though, this aside, my question still stands as which is faster assuming both would succeed?

P.S。如果这是重复，我很抱歉。它基本就足够了，但我找不到类似的东西。

推荐答案

依赖很多。

如果在加载后使主键索引成群集，则整个表将被重写为聚簇索引实际上不是索引，它是数据的逻辑顺序。插入时的执行计划将取决于确定计划时的索引，如果聚簇索引到位，它将在插入之前进行排序。您通常会在执行计划中看到这一点。

If you make the primary key index clustered after the load, the entire table will be re-written as the clustered index isn't really an index, it is the logical order of the data. Your execution plan on the inserts is going to depend on the indexes in place when the plan is determined, and if the clustered index is in place, it will sort prior to the insert. You will typically see this in the execution plan.

如果您将主键设为一个简单约束，它将是一个常规（非聚集）索引，该表将只需按优化器确定的顺序填充并更新索引。

If you make the primary key a simple constraint, it will be a regular (non-clustered) index and the table will simply be populated in whatever order the optimizer determines and the index updated.

我认为整体性能最快（加载临时表的这个过程）通常是写数据作为一个堆，然后应用（非聚集）索引。

I think the overall quickest performance (of this process to load temp table) is usually to write the data as a heap and then apply the (non-clustered) index.

但是，正如其他人所指出的那样，索引的创建可能会失败。此外，临时表不是孤立存在的。据推测，有一个最佳索引可以从下一步读取数据。该索引需要就位或创建。 这个是你必须在这里权衡速度以获得可靠性（首先应用PK和任何其他约束）和以后的速度（如果你将要有一个聚集索引，至少）。

However, as others have noted, the creation of the index could fail. Also, the temp table does not exist in isolation. Presumably there is a best index for reading the data from it for the next step. This index will need to either be in place or created. This is where you have to make a tradeoff of speed here for reliability (apply the PK and any other constraints first) and speed later (have at least the clustered index in place if you are going to have one).

这篇关于在临时表上创建主键 - 何时？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！