Duplicate Identical ROWs PROBLEM in table with unique Clustered Index

Hi !
We are on SQL SERVER 2000 SP3.
We recently had a problem where it appeared (after an extensive reseach via
DBCC checkdb)
we had duplicate identical rows enter a table that had a unique clustered
index defined on most
columns in the table. DBCC checkdb kept reporting corrupt index pages .
After running a number of DBCC checkdb and DBCC reindex the database was
flagged
as (Suspect). We correted the (Suspect) problem by re-loading a copy of an
already corrupt database and deleting duplicate rows (set rowcount 1; DELETE
..).
After that exersise, DBCC checkdb showed no more problems.
One thing to note, prior to 2000, the create index syntax allowed for the
following options:
IGNORE_DUP_ROW and ALLOW_DUP_ROW.
In 2000, those options were dropped from the syntax ... I wonder if this
created a hole ...
The question is: Why did the database allowe duplicate rows to enter into a
table with
a UNIQUE CLUSTERED INDEX defined '
Can anyone offer any help ' Many thanks !
Here is the table:
CREATE TABLE dbo.CROSS_REF_DATA_IDENTIFIERS (
ASSET_ID bigint NOT NULL ,
ID_CD varchar (3) AS NOT NULL ,
ID_NUMBER varchar (30) AS NOT NULL ,
ID_EFFECTIVE_DATE datetime NOT NULL ,
PROCESSED int NULL ,
CONSTRAINT CROSS_REF_DATA_IDENTIFIERS_PK PRIMARY KEY CLUSTERED
(ASSET_ID,
ID_CD,
ID_NUMBER,
ID_EFFECTIVE_DATE
) ON PRIMARY ,
CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_X_CORE_INF
ORMATION FOREIGN KEY
(ASSET_ID) REFERENCES dbo.X_CORE_INFORMATION (ASSET_ID),
CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_CDS_VENDOR
_SRC FOREIGN KEY
(ID_CD) REFERENCES dbo.CDS_VENDOR_SRC ( CODE)
) ON PRIMARYHi,Dean
Ok, what is about sample data?
"Dean Nates" <dnatenzon@.alexandra.net> wrote in message
news:uAYMKNrBEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Hi !
> We are on SQL SERVER 2000 SP3.
> We recently had a problem where it appeared (after an extensive reseach
via
> DBCC checkdb)
> we had duplicate identical rows enter a table that had a unique clustered
> index defined on most
> columns in the table. DBCC checkdb kept reporting corrupt index pages .
> After running a number of DBCC checkdb and DBCC reindex the database was
> flagged
> as (Suspect). We correted the (Suspect) problem by re-loading a copy of an
> already corrupt database and deleting duplicate rows (set rowcount 1;
DELETE
> ..).
> After that exersise, DBCC checkdb showed no more problems.
> One thing to note, prior to 2000, the create index syntax allowed for the
> following options:
> IGNORE_DUP_ROW and ALLOW_DUP_ROW.
> In 2000, those options were dropped from the syntax ... I wonder if this
> created a hole ...
> The question is: Why did the database allowe duplicate rows to enter into
a
> table with
> a UNIQUE CLUSTERED INDEX defined '
> Can anyone offer any help ' Many thanks !
> Here is the table:
> CREATE TABLE dbo.CROSS_REF_DATA_IDENTIFIERS (
> ASSET_ID bigint NOT NULL ,
> ID_CD varchar (3) AS NOT NULL ,
> ID_NUMBER varchar (30) AS NOT NULL ,
> ID_EFFECTIVE_DATE datetime NOT NULL ,
> PROCESSED int NULL ,
> CONSTRAINT CROSS_REF_DATA_IDENTIFIERS_PK PRIMARY KEY CLUSTERED
> (ASSET_ID,
> ID_CD,
> ID_NUMBER,
> ID_EFFECTIVE_DATE
> ) ON PRIMARY ,
> CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_X_CORE_INF
ORMATION FOREIGN KEY
> (ASSET_ID) REFERENCES dbo.X_CORE_INFORMATION (ASSET_ID),
> CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_CDS_VENDOR
_SRC FOREIGN KEY
> (ID_CD) REFERENCES dbo.CDS_VENDOR_SRC ( CODE)
> ) ON PRIMARY
>
>|||Here is the actual sample of what DBCC checkdb reported:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 6, object ID 1065770854, index ID 0. Chain linkage
mismatch. (1:574284)->next = (1:574285), but (1:574285)->prev = (1:481988 )
.
"Dean Nates" <dnatenzon@.alexandra.net> wrote in message
news:uAYMKNrBEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Hi !
> We are on SQL SERVER 2000 SP3.
> We recently had a problem where it appeared (after an extensive reseach
via
> DBCC checkdb)
> we had duplicate identical rows enter a table that had a unique clustered
> index defined on most
> columns in the table. DBCC checkdb kept reporting corrupt index pages .
> After running a number of DBCC checkdb and DBCC reindex the database was
> flagged
> as (Suspect). We correted the (Suspect) problem by re-loading a copy of an
> already corrupt database and deleting duplicate rows (set rowcount 1;
DELETE
> ..).
> After that exersise, DBCC checkdb showed no more problems.
> One thing to note, prior to 2000, the create index syntax allowed for the
> following options:
> IGNORE_DUP_ROW and ALLOW_DUP_ROW.
> In 2000, those options were dropped from the syntax ... I wonder if this
> created a hole ...
> The question is: Why did the database allowe duplicate rows to enter into
a
> table with
> a UNIQUE CLUSTERED INDEX defined '
> Can anyone offer any help ' Many thanks !
> Here is the table:
> CREATE TABLE dbo.CROSS_REF_DATA_IDENTIFIERS (
> ASSET_ID bigint NOT NULL ,
> ID_CD varchar (3) AS NOT NULL ,
> ID_NUMBER varchar (30) AS NOT NULL ,
> ID_EFFECTIVE_DATE datetime NOT NULL ,
> PROCESSED int NULL ,
> CONSTRAINT CROSS_REF_DATA_IDENTIFIERS_PK PRIMARY KEY CLUSTERED
> (ASSET_ID,
> ID_CD,
> ID_NUMBER,
> ID_EFFECTIVE_DATE
> ) ON PRIMARY ,
> CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_X_CORE_INF
ORMATION FOREIGN KEY
> (ASSET_ID) REFERENCES dbo.X_CORE_INFORMATION (ASSET_ID),
> CONSTRAINT FK_CROSS_REF_DATA_IDENTIFIERS_CDS_VENDOR
_SRC FOREIGN KEY
> (ID_CD) REFERENCES dbo.CDS_VENDOR_SRC ( CODE)
> ) ON PRIMARY
>
>sql