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

Article translations Article translations
Article ID: 284440 - View products that this article applies to.
This article was previously published under Q284440
BUG #: 58247 (SQLBUG_70)
Bug #: 235592 (SHILOH_bugs)
Expand all | Collapse all

On This Page


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 2 Table 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 2 Data 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 2 Table 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 2 Index 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 tempdb

drop table #t

create table #t(col1 uniqueidentifier DEFAULT (NewID()), CONSTRAINT main UNIQUE NONCLUSTERED (col1))

insert into #t values (NewID())

set col1 = CASE WHEN 1=1 THEN NEWID() ELSE NULL end -- results in checktable errors
-- set col1 = NEWID() -- doesn't result in the errors

dbcc traceon(3604)

dbcc checktable(#t)


Article ID: 284440 - Last Review: March 14, 2006 - Revision: 4.2
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
kbbug kbfix kbsqlserv2000sp1fix KB284440

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from