You are currently offline, waiting for your internet to reconnect

FIX: UPDATE with CASE Statement That Uses NEWID() Function Causes Errors 8951, 8952, 8955 and 8956

This article was previously published under Q284440
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58247 (SQLBUG_70)
Bug #: 235592 (SHILOH_bugs)
An UPDATE statement that modifies data by using a CASE statement and the NEWID function may report the following error message when running a DBCC CHECKTABLE function on the underlying table:
Server: Msg 8951, Level 16, State 1, Line 2Table Corrupt: Table '#t__00000000000C' (ID 165575628). Missing or invalid key in index 'main' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 2Data row (1:87:0) identified by (RID = (1:87:0) ) has index values (col1 = 9EF944CD-CBB4-4E7E-A1A4-A2E5B1565FC1,).
Server: Msg 8952, Level 16, State 1, Line 2Table Corrupt: Database 'tempdb', index '#t__00000000000C.main' (ID 165575628) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 2Index row (1:104:0) with values (col1 = 019973DC-D2E6-4840-AEB8-25DC262A1840,) points to the data row identified by (RID = (1:87:0)).

These error messages may occur only if all of the following conditions are true:
  • The table has a data type of uniqueidentifier on one of the columns.
  • The preceding column is updated with a CASE statement along with the NEWID function.
  • There is a unique nonclustered index on the column with the uniqueidentifier datatype.
Avoid the use of a CASE statement in an UPDATE statement.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

Microsoft has confirmed that this is a problem in SQL Server 2000.

Steps to Reproduce Behavior

To reproduce the behavior, follow these steps:
use tempdbgodrop table #tgocreate table #t(col1 uniqueidentifier DEFAULT (NewID()), CONSTRAINT main UNIQUE NONCLUSTERED (col1))goinsert into #t values (NewID())goUPDATE #tset col1 = CASE WHEN 1=1 THEN NEWID() ELSE NULL end -- results in checktable errors-- set col1 = NEWID() -- doesn't result in the errorsgodbcc traceon(3604)godbcc checktable(#t)go				

Article ID: 284440 - Last Review: 01/16/2015 21:23:24 - Revision: 4.2

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB284440