SQL Server:索引已经存在于全局温度表上

Question

在SQL Server 2012 sp3 v.11.0.6020.0（x64）中，我有一个存储过程，该过程测试存在全局临时表（##MyTable，例如）并创建它 - 如果当然找不到。

IF OBJECT_ID ( 'tempdb..##MyTable' ) IS NULL
    CREATE TABLE ##MyTable
    (
        Key1 smallint
      , Key2 nvarchar(16)
      , Value1 char(3)
    );

稍后在过程中，它测试表是否有行，如果需要 - 将其填充。

IF NOT EXISTS ( SELECT * FROM ##MyTable )
BEGIN
    INSERT INTO ##MyTable
    SELECT Key1, Key2, Value1
    FROM SourceTable
    WHERE ...
    CREATE NONCLUSTERED INDEX IX_MyTable ON ##MyTable ( [Key1], [Key2] ); 
END

我确定key1和key2是唯一的，因为它们是源表上的主要密钥。

然后，在任何一种情况下（表已经存在是否存在），存储过程查询表。不用说，SP逻辑比这要复杂得多。

该表中填充了来自7种不同来源的客户数据；通常，插入近100万行需要几秒钟。从理论上讲，所有插入物没有机会插入## mytable插入0（零）行。

存储过程是通过应用程序调用的：此应用程序通常在早上启动并在晚上关闭。

从理论上讲，可能存在冲突，用户试图插入数据并创建索引，而另一个已经在做同样的索引。但是，同一用户始终不太可能发生。如果用户在几分钟后再次尝试（表和索引已经存在）。

这对所有用户都很好（接近100个用户），但是一个不断错误的特定用户： The operation failed because an index or statistics with name 'IX_MyTable' already exists on table ##MyTable。

除了我已经在考虑将全球临时表作为常规表，有人可以向我解释这种行为吗？

事先感谢任何会有帮助的人！

Answer 1

您将在这种情况下体验这种行为：

1. 您创建表。它是空的。
2. insert查询是运行的，但没有插入行。
3. 创建了索引。

在下一个运行中，您将有一个空表并再次尝试insert。

这很容易解决。只需使用try/catch块或测试即可查看索引在创建之前是否存在。或者，更好的是，创建表时创建索引。除非您插入大量数据，否则开销不应该太糟糕。

Answer 2

如果您在表创建块中移动创建索引语句本身。

IF OBJECT_ID ( 'tempdb..##MyTable' ) IS NULL
BEGIN
    CREATE TABLE ##MyTable
    (
        Key1 smallint
      , Key2 nvarchar(16)
      , Value1 char(3)
    );
    CREATE NONCLUSTERED INDEX IX_MyTable ON ##MyTable ( [Key1], [Key2] ); 
END

Answer 3

错误解释自身，您必须在创建表格时创建索引，当它已经存在时，您无法创建索引

IF OBJECT_ID ( 'tempdb..##MyTable' ) IS NULL
Begin
CREATE TABLE ##MyTable
(
    Key1 smallint
  , Key2 nvarchar(16)
  , Value1 char(3)
);
CREATE NONCLUSTERED INDEX IX_MyTable ON ##MyTable ( [Key1], [Key2] );
END 

在您存储的过程中，您要在表为空时创建索引内部条件，因此每次从表中删除所有数据时，它都会尝试创建索引。