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_Payment_Simulation (
file_no int NOT NULL,
row_no int NULL,
related_claim_seq int NULL,
claim_id int NULL,
patient_control_no varchar(30) NULL,
patient_id varchar(100) NULL
-- CONSTRAINT XPKHClaim_Payment_Simulation PRIMARY KEY CLUSTERED
(file_no, row_no, related_claim_seq)
)
ALTER TABLE dbo.Claim_Payment_Simulation ADD CONSTRAINT
XPKClaim_Payment_Simulation
UNIQUE CLUSTERED (file_no, row_no, related_claim_seq) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX XIE1Claim_Payment_Simulation ON
dbo.Claim_Payment_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_Payment_Simulation (file_no, related_claim_seq,
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_Payment_Simulation
SET @claim_id =
CASE WHEN CHARINDEX ('.', patient_control_no) = 0
THEN SUBSTRING (patient_control_no, 9, 30)
ELSE SUBSTRING (patient_control_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_Payment_Simulation
--SELECT * FROM dbo.Claim_Payment_Simulation
--DROP TABLE dbo.Claim_Payment_Simulation
GO
