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

SYMPTOMS

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.

STATUS

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.

MORE INFORMATION

Steps to Reproduce Behavior

To reproduce the behavior, follow these steps:

use tempdb
go

drop table #t
go

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

insert into #t values (NewID())
go

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

dbcc traceon(3604)
go

dbcc checktable(#t)
go