asked
Duplicate Record
In my application, I have a table with 40 columns and this table has approx 15000000 row. recently i noticed some duplicate record inserted in table. in this table I have a column RowId with clustered index, and some another column like MobNo varchar(10), AccountNo varchar(10), RecAmt Decimal(102), TransactionDate DateTime, CreatedDate DateTime. This application access by many clients at a time. recently i notice some duplicate record inserted in table.and time difference between two record is maximum 60 sec. we can't create unique index on this table with AccountNo + MobNo + TransactionDate + RecAmt because same request can be arrived in system for same mob no after 10 to 20 minuts. plz suggest me how i can avoid such duplicate entry in my system. Thanks Mukesh Kumar
So you are saying the same AccountNo + MobNo + TransactionDate + RecAmt can be duplicated after 10 minutes, but not within 60 secs? You can't have a design that allows for some dupes but not others (well actually you can, but it's not a good design). You need to understand what can make a distinction between those rows that are dupes are those that are not. Add something to the data that makes the distinction and then build that into the application and database.
If absolutely the only thing that marks the rows as distinct is that lag in time, you could add a datetime column that you truncate to the minute level (or round to the nearest ten minutes, or something).
yes, rows can be duplicate. means user can send separate request next time. but systematically it will take at least 5 min for a separate request. in this case this rows in inserted in table with single request. every day approx 100000 to 150000 row inserted in table but only 5 or 10 rows inserted with duplicate value.
14 People are following this question.
