BUG: Index Not Used on Temp Table Created Inside Procedure (98860)

SYMPTOMS

Output from SET SHOWPLAN ON reveals that an index (clustered or non-clustered) on a temporary table created inside a stored procedure is not used if the temporary table is queried from a nested procedure (even using a search clause with a very high selectivity). For example:

   procedure P1

      Creates a temporary table including a unique id column
      Populates it with data
      Builds an index on the unique id column
      exec procedure P2
         which SELECT's a single row from the temporary table
         using the unique id in the search clause

Note that this only happens if the outer stored procedure P1 is executed right after the two procedures are created. If the server is shut down and restarted and P1 is executed again, the index will be used from that point onward until the procedures are dropped and recreated.

WORKAROUND

Shut down and restart SQL Server if you run into the above situation. This means that those sites that shut down the server frequently will probably not see this problem. Another workaround is to replace the temporary table with a permanent table.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2. We are researching this problem and will post more information here in the Microsoft Knowledge Base as it becomes available.