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