BUG: Index Not Used on Temp Table Created Inside Procedure

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, version 4.2

       NT:  232 (4.2)

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

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 SQL Server version 4.2 for OS/2 and 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.