Microsoft KB Archive/235401

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

Article ID: 235401

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q235401



BUG #: 55906 (SQLBUG_70)



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.

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.



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



STATUS
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:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Keywords: kbbug kbfix KB235401

-

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

© Microsoft Corporation. All rights reserved.