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

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: This error only occurs if all of the following conditions are met:

• 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.

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.

To work around 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.

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:For more information, contact your primary support provider.