BUG: 8623 Error with INSERT-SELECT when Nullable Clustered Keys not Referenced

• Microsoft SQL Server version 7.0

SYMPTOMS

Running an INSERT SELECT statement into a table that has a clustered index, in which all fields allow NULLs, may fail with the following error message:

Server: Msg 8623, Level 16, State 2, Line 5 Internal Query Processor Error: The query processor could not produce a query plan.

• The table has a clustered index in which all fields allow NULLs.



• The clustered index fields are not referenced in the SELECT statement, so NULL values are to be inserted for all fields in the index.



• A sufficient amount of rows are being inserted such that SQL Server determines it would be faster to sort the data before inserting into the clustered index.



• Only one processor is available at the time the query plan is being generated. This could be for any of the following reasons:
  
  
  • The computer has a single processor.
  
  
  
  • The computer has multiple processors, but the Maximum Degree of Parallelism (MAX DOP) has been set to 1, either in sp_configure or with a query hint.
  
  
  
  • The computer has multiple processors, but is under such a sufficient load that they are all being utilized at the time the query plan is being generated.

CAUSE

When generating the plan for the sort with a single thread, the optimizer code does not expect NULL values for all of the fields to be sorted.

WORKAROUND

To workaround this issue, choose any of the following options:

• Change the clustered index to include at least one field that does not allow NULL values.



• Change the clustered index to a non-clustered index.



• Reference at least one of the clustered index keys in the INSERT SELECT statement so there are values inserted into the field.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords          : kbSQLServ700bug 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 23, 1999