在临时表上创建主键 - 何时进行？

Question

我有一个处理大量数据的存储过程。 我将该数据插入到临时表中。 整个事件流程大致如下：

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表的主键最佳？ 我认为应该在插入所有数据后创建主键约束/索引，因为主键信息被创建时需要重新组织索引。但我意识到我的基本假设可能是错误的...

如果相关，我使用的数据类型是real。在#TempTable表中，Col1和Col4将构成我的主键。

更新：在我的情况下，我复制了源表的主键。我知道构成我的主键的字段始终是唯一的。如果我在最后添加主键，则不必担心alter table失败。

尽管如此，我的问题依然存在，即两者都成功的情况下哪一个更快？

- Frank V

Answer 1

这很大程度上取决于情况。

如果您在加载后将主键索引设置为聚集索引，则整个表将被重新编写，因为聚集索引实际上不是索引，而是数据的逻辑顺序。插入操作的执行计划将取决于计划确定时所处的索引，如果聚集索引已经存在，则在插入之前会进行排序。通常，在执行计划中可以看到这一点。

如果您将主键设置为简单约束，则它将成为常规（非聚集）索引，并且表将以优化器确定的任何顺序进行填充并更新索引。

我认为通常最快的性能（用于加载临时表的过程）是将数据写入堆，然后应用（非聚集）索引。

然而，正如其他人所指出的那样，索引的创建可能会失败。此外，临时表并不是孤立存在的。假定对于下一步从中读取数据的最佳索引。该索引需要已经存在或者必须要创建。这就是你必须在速度和可靠性之间权衡的地方（先应用PK和任何其他约束，然后尽可能快地创建至少一个聚集索引）。

- Cade Roux

---

有趣。谢谢你。有帮助。你能举些例子吗？ - Frank V

@Cade，对于聚集索引，您指的是磁盘上的物理顺序，而不是逻辑顺序（表没有逻辑顺序）。 - Peter Radocchia

不，磁盘上的物理顺序可以是任何东西。聚集索引只是在B树索引中存储在叶子节点中的数据，而不是在堆中存储的数据。在SQL Server和磁盘上仍然可能存在碎片化。 - Cade Roux

我明白你的意思。收到。 - Peter Radocchia

Answer 2

如果您的数据库的恢复模式设置为简单或批量记录，则SELECT ... INTO ... UNION ALL可能是最快的解决方案。 SELECT .. INTO是一种批量操作，而批量操作的日志记录最小化。

例如：

-- first, create the table
SELECT ...
INTO #TempTable
FROM MyTable
WHERE ...
UNION ALL
SELECT ...
FROM MyTable2
WHERE ...

-- now, add a non-clustered primary key:
-- this will *not* recreate the table in the background
-- it will only create a separate index
-- the table will remain stored as a heap
ALTER TABLE #TempTable ADD PRIMARY KEY NONCLUSTERED (NonNullableKeyField)

-- alternatively:
-- this *will* recreate the table in the background
-- and reorder the rows according to the primary key
-- CLUSTERED key word is optional, primary keys are clustered by default
ALTER TABLE #TempTable ADD PRIMARY KEY CLUSTERED (NonNullableKeyField) 

否则，Cade Roux提供了很好的建议：在之前或之后。

- Peter Radocchia

Answer 3

在插入数据之前，最好先创建主键 - 如果主键是在自增列上的，则插入将按顺序进行，因此不会有任何区别。

- Justin

---

我不会使用identity。你在我更新问题的时候发布了这个。我的主键将由NUMERIC(18,0)和一个VARCHAR(25)字段组成。 - Frank V

默认情况下，主键是聚集的。这将根据PK值顺序排列数据。我同意这个答案，在插入之前应该发生。还要注意：如果添加额外的非聚集索引，则在创建聚集PK之后，SQL Server将重建非聚集索引。 - DBAndrew

@Justin 在插入数据之前，我该如何创建主键？你能给出一个带有小示例查询的完整建议吗？ - itro

Answer 4

比性能考虑更重要的是，如果您不能绝对、100%确定将插入唯一值到表中，请先创建主键。否则，将无法创建主键。

这可以防止您插入重复/错误数据。

- Jeff Meatball Yang

---

这对我来说不是问题。我知道对于一些人可能是问题，但对我来说完全不是问题。 - Frank V

我认为这是有意义的，因为它可以更清晰地展示你所尝试逻辑上要做的事情，并且在以前的假设不再安全（例如由于数据库其他部分的更改导致不再确定插入后表中没有重复数据）时，让引擎帮助你。 - binki

Answer 5

如果在创建表时添加主键，则第一次插入将是免费的（无需检查）。第二次插入只需要检查其是否与第一个不同。第三次插入则需要检查两行，以此类推。这些检查将是索引查找，因为有唯一约束。

如果在所有插入之后添加主键，则每一行都必须与其他每一行匹配。所以我的猜测是早期添加主键更便宜。

但是也许Sql Server有一种非常聪明的检查唯一性的方法。所以如果你想确保，就要进行度量！

- Andomar

---

SQL Server 确实有一种非常聪明的检查唯一性的方式。 - Geoff Griswald

Answer 6

我在思考如何改进一个非常“昂贵”的存储过程，它涉及跨表格的大量检查，并找到了这个答案。在存储过程中，打开了几个临时表并相互引用。我在CREATE TABLE语句中添加了主键（即使我的选择使用WHERE NOT EXISTS语句插入数据并确保唯一性），我的执行时间大大缩短。我强烈建议使用主键。即使您认为不需要它，也应该至少尝试一下。

- user1076406

---

问题的背景不是要不要创建主键，而是在何时创建更有效率——是在数据预填充之前还是之后。在这个问题之前，结论是使用键/索引可以提高执行时间。 - Frank V

Answer 7

我认为在你的情况下这并没有什么显著的区别：

• 要么你每次单独插入时都会支付一点点罚款
• 要么在所有插入完成后，你将支付更大的罚款，但只需支付一次

当你在插入开始之前预先创建它时，如果PK值不是系统创建的，你可能会在数据插入时捕获PK违规。

但除此之外 - 实际上没有太大的区别。

Marc

- marc_s

---

拆分和日志记录等方面的范围？这不需要考虑吗？ - Frank V

Answer 8

我本来不打算回答这个问题，因为我对此并不是100%有信心。但是看起来你没有得到太多的回应...

我的理解是PK是一个唯一索引，当您插入每条记录时，索引会被更新和优化。所以...如果您先添加数据，然后创建索引，那么索引只会被优化一次。

因此，如果您确信您的数据是干净的（没有重复的PK数据），那么我建议先插入，然后再添加PK。

但是，如果您的数据可能有重复的PK数据，那么我建议先创建PK，这样它就会尽快失败。

- John MacIntyre

---

感谢您的回复。事实上，我确信我不会遇到重复问题... - Frank V

Answer 9

当您在表创建时添加PK时，插入检查是O(Tn)（其中Tn是“第n个三角形数”，即1 + 2 + 3 ... + n），因为当您插入第x行时，它会与先前插入的“x-1”行进行检查。

当您在插入所有值之后添加PK时，检查器为O(n^2)，因为当您插入第x行时，它会与所有n现有行进行检查。

第一个显然更快，因为O(Tn)小于O(n^2)

P.S. 示例：如果您插入5行，则为1 + 2 + 3 + 4 + 5 = 15次操作，而不是5^2 = 25次操作

- Alex from Jitbit