easily repeatable index corruption in SQL Server 2000 (8.00.2050,post-sp4)

[brian.j.parker]

One of the developers where I work found that some of his code would
regularly cause index corruption in SQL Server 2000, post-sp4.
@@version is 8.00.2050 and I can repeat this, so I can't (re-)apply
the hotfix for KB article 929440.

I know the code is not a good way to do what he's doing, which I
explained to him. But corrupting the index on the table probably
isn't the right reaction for SQL Server.

A script to recreate the error follows (it's a slightly generic
version of what he was doing). You can create the table in tempdb and
drop it when done. You will get a pile of "Missing or invalid key in
index..." errors from the DBCC CHECKTABLE.

USE tempdb
SET NOCOUNT ON
--select @@version
GO

/* RUN ONCE
CREATE TABLE dbo.Claim_Payme nt_Simulation (
file_no int NOT NULL,
row_no int NULL,
related_claim_s eq int NULL,
claim_id int NULL,
patient_control _no varchar(30) NULL,
patient_id varchar(100) NULL
-- CONSTRAINT XPKHClaim_Payme nt_Simulation PRIMARY KEY CLUSTERED
(file_no, row_no, related_claim_s eq)
)

ALTER TABLE dbo.Claim_Payme nt_Simulation ADD CONSTRAINT
XPKClaim_Paymen t_Simulation
UNIQUE CLUSTERED (file_no, row_no, related_claim_s eq) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX XIE1Claim_Payme nt_Simulation ON
dbo.Claim_Payme nt_Simulation (claim_id)
GO

*/

DECLARE @file_no int, @claim_id int, @count int
SET @file_no = 1
SET @count = 1
WHILE @count < 10000 BEGIN
INSERT dbo.Claim_Payme nt_Simulation (file_no, related_claim_s eq,
patient_control _no)
VALUES (@file_no, @count * 2, CAST (@count * 3 AS varchar (8)) + 'A.'
+ CAST (@count * 4 AS varchar) )

SET @count = @count + 1
END
--DBCC checktable('dbo .Claim_Payment_ Simulation')
GO
DECLARE @file_no int, @claim_id int, @count int
SET @file_no = 1
UPDATE dbo.Claim_Payme nt_Simulation
SET @claim_id =
CASE WHEN CHARINDEX ('.', patient_control _no) = 0
THEN SUBSTRING (patient_contro l_no, 9, 30)
ELSE SUBSTRING (patient_contro l_no, CHARINDEX ('.',
patient_control _no) + 1, 30)
END,
claim_id =
CASE WHEN ISNUMERIC (@claim_id) = 1
THEN
CASE WHEN CAST (@claim_id AS bigint) <= 2147483647
THEN @claim_id
ELSE -99999
END
ELSE -88888
END
WHERE file_no = @file_no -- HOWEVER, if the where clause is NOT
included in the update statement, there will be no index corruption

DBCC checktable('dbo .Claim_Payment_ Simulation')
GO

TRUNCATE TABLE dbo.Claim_Payme nt_Simulation
--SELECT * FROM dbo.Claim_Payme nt_Simulation
--DROP TABLE dbo.Claim_Payme nt_Simulation
GO

[Erland Sommarskog]

(br************ @gmail.com) writes:

One of the developers where I work found that some of his code would
regularly cause index corruption in SQL Server 2000, post-sp4.
@@version is 8.00.2050 and I can repeat this, so I can't (re-)apply
the hotfix for KB article 929440.

I know the code is not a good way to do what he's doing, which I
explained to him. But corrupting the index on the table probably
isn't the right reaction for SQL Server.

I was able to reproduce the error with SQL 2000 SP4. I did not happen
on SQL 2005 SP2. I don't know if there is a later build of SQL 2000
which addresses this. But since there appears to be a workaround, and
SQL 2000 has fallen out of mainstream support, I doubt that this will
ever be fixed in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx