2

In Sql Server 2012 SP3 v.11.0.6020.0 (X64), I have a stored procedure which tests for the existence of a global temporary table (##MyTable , e.g.) and creates it - if not found, of course.

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

Later in the procedure, it tests if the table has rows and - if necessary - populates it.

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

I am sure that Key1 and Key2 are unique, since they are primary keys on source table.

Then, in either case (the table already existed or not) the stored procedures queries the table. Needlesss to say, the sp logic is much more complex than this.

The table is populated with customers' data coming from 7 different sources; usually, it takes a couple of seconds to insert almost 1 million of rows. Theoretically there is no chance that the all of the INSERT INTO ##MyTable insert 0 (ZERO) rows.

The stored procedure is called by an application: this application is usually launched in the morning and closed at night.

Theoretically, there could be conflicts, a user tries to insert data and create the index while another one was already doing the same. But it's very unlikely that it happens always to the same user; it should be impossible, if that user tries again a few minutes later (table and index already exists).

This works fine for all of the user (near 100), but a specific one, who keeps getting an error: The operation failed because an index or statistics with name 'IX_MyTable' already exists on table ##MyTable.

Aside from the fact that I'm already thinking about making the global temporary table a regular one, could anyone please explain to me this behaviour?

Thanks in advance to anyone who will help!

M.Turrini
  • 738
  • 1
  • 4
  • 19
  • 1
    What needs explaining? The table already seems to have the index in question. If I had to guess, it is because you haven't dropped a previous version of the table. – Gordon Linoff Mar 06 '17 at 12:30
  • I'm not getting your code all that well, it raises a lot of questions on my end. However, I would assume that 2 instances run into the `if not exists` flow. Possibly because there's one which does not insert data in your insert statement (but will still create the index)? – Jens Mar 06 '17 at 12:33
  • @GordonLinoff : English is not my mother-tongue, but I understand the error by itself; what confuses me is why is always THE SAME USER to get this error. There is no order of logging in to the database, there's no apparent relation with daytime, Just ONE (and always the same one) user gets this error: all the other users can get data from the table with no issue at all. – M.Turrini Mar 06 '17 at 14:14

3 Answers3

3

You would experience this behavior under these circumstances:

  1. You create the table. It is empty.
  2. The insert query is run, but inserts no rows.
  3. The index is created.

On the next run, you will have an empty table and attempt the insert once again.

This is easy enough to work around. Just use a try/catch block or test to see if the index exists before creating it. Or, better yet, create the index when you create the table. Unless you are inserting a lot of data, the overhead shouldn't be too bad.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is what I was thinking as well. – Jens Mar 06 '17 at 12:35
  • In theory, you should be wrong (wait, I'm going to elaborate on this); the table is populated from customers' data from 7 sources and usually contains 1million of rows. In theory, by no means it should be empty the first time the CREATE INDEX is executed. Still, your explaination seems the most relevant to the case - at least to me. – M.Turrini Mar 06 '17 at 14:09
1

The Error explains itself, you must create the index while creating the table, you cant create an index when it already exists

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

in your Stored Procedure you are creating index inside condition when the table is empty, so every time you delete all data from table, it is trying to create index.

Pream
  • 517
  • 4
  • 10
  • First of all, you forgot a BEGIN ... END pair: without it, the index would really be created each time the sp is executed :) I forgot to mention that table is static: once created and populated, it is never DELETEd, TRUNCATEd or DROPped: it's a table with customer's code (Key2), origin (Key1) and name (value), used just for a quick reference, instead of a view with several UNIONs from 7 (seven!) different sources. Not the best solution, I agree and very likely we'll change that, but... I don't get WHY the error – M.Turrini Mar 06 '17 at 13:53
  • @M.Turrini yeah i forgot the begin..end, updated now. – Pream Mar 06 '17 at 15:24
1

Better if you move your Create Index statement in Table creation block itself..

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
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
  • 1
    True, but I put it AFTER the INSERT INTO... for performance considerations: I didn't come to this by myself, but I read this somewhere. Your solution should solve the problem, but... it (the problem) should not arise anyway: table is quite static, once is populated, table is never DELETEd, TRUNCATEd or DROPped, so I expect the index to be created just once. – M.Turrini Mar 06 '17 at 13:57