By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Sep 29 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(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****@sommarskog.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

Sep 29 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.