Microsoft KB Archive/98860

= BUG: Index Not Used on Temp Table Created Inside Procedure =

Article ID: 98860

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q98860



BUG# 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

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.

Additional query words: Windows NT

Keywords: kbprogramming KB98860

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.