An UPDATE statement, which uses a join, may fail to update all of the qualifying rows if all the following conditions are true:

•	The ALTER TABLE command is used to add a column on the table that is to be updated.
•	The UPDATE statement updates the new column.
•	The table to be updated has a unique clustered index defined on the join column.
•	The update is performed on a multi-processor computer.
•	The update results in page-splits.
•	Microsoft SQL Server chooses a parallel scan for the update. That is, SQL Server uses multiple processors to perform the update.

Back to the top

You cannot use parallel table scans if pages might split during the scan.

Back to the top

RESOLUTION

The resolution is to use a serial scan instead of a parallel scan.

Back to the top

Here are two ways to work around the problem:

•	Use the "MAXDOP 1" query hint to perform the update.
•	Set the max degree of parallelism configuration option to 1 to suppress parallel plan generation as follows: EXEC sp_configure 'max degree of parallelism', 1 GO RECONFIGURE with Override GO

Back to the top

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.

Back to the top

REFERENCES

For more information about the MAXDOP query hint and configuration setting, refer to the "UPDATE (T-SQL)" topic or "max degree of parallelism Option" topic in SQL Server Books Online.

Back to the top