BUG: Index Not Used on Temp Table Created Inside ProcedureID: Q98860
|
BUG# OS/2: 1679 (4.2)
NT: 232 (4.2)
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
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.
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.
Additional query words: Windows NT
Keywords : kbprg kbbug4.20 SSrvServer SSrvWinNT
Version : 4.2 | 4.2 4.2a
Platform : OS/2 WINDOWS
Issue type :
Last Reviewed: March 16, 1999