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 различных источников; обычно для вставки почти 1 миллиона строк требуется несколько секунд. Теоретически нет никаких шансов, что все INSERT INTO ##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

Лучше, если вы переместите свой оператор Create Index в сам блок создания таблицы.

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 

в своей хранимой процедуре вы создаете индекс внутри условия, когда таблица пуста, поэтому каждый раз, когда вы удаляете все данные из таблицы, она пытается создать индекс.