November 17, 2005 at 3:03 am
I have recently created an indexed view on a database to improve performance with reports. Since that time errors have occurred intermittently when inserts are made to the base tables participating in the indexed view.
When an insert fails the error is Cannot insert duplicate in the indexed view's unique index error. However, on examination, the inserted record is not a duplicate according to the unique index on the indexed view.
The indexed view joins 3 tables (Customers, Products and Purchases). The unique clustered index on the view is the Customer ID and Product ID. This combination is unique in the base tables (it is the unique index on Purchases). The table where the inserts are failing is the Purchases table. Every day hundreds of inserts into the Purchases table happen successfully in the database, but every few days one fails with the Cannot Insert Duplicate error. FYI, inserts are always one record at a time.
When this error last happened, I could see that the inserted record was not a duplicate because the customer did not already have a Purchase record for the given product. I then dropped the unique clustered index on the view and manually inserted the record. It worked. I then tried recreating the unique clustered index on the view and this worked also.
The indexed view is on a database that is the subscriber in a merge replication topology - this may or may not be relevant. The SQL Server is 2000 Standard Edition, service pack 3.
What could be happening to cause this?
November 18, 2005 at 9:22 am
We've seen this happen on a few customer databases over the past couple years, mostly when running on Windows 2000. All the problems I have seen were fixed by service pack 4. And before SP4 was released, we used a hotfix from Microsoft to fix the problem. So I would suggest installing SQL 2000 SP4 if that is an option for you.
November 30, 2005 at 6:16 am
Thanks, that is good news, I'll try that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply