I have inherited the following situation in ASP.Net website + SQL server database:
There is a big table(many rows, many columns) with a unique index on a field called PartnerRef, varchar(50). It also has a primary key, int, let's call it UniqueId. For 8-10 years a lot of code was written relying on the uniqueness of PartnerRef field. Many places/queries using [email protected] criteria to find one specific record. All of the sudden, we received the news that partners will start re-using values, sometimes even after just one week, so a new record might have to be created, while another one already exists having the same PartnerRef. Removing the unique index and changing all the code is not an option right now due to time pressure. Quick solution I implemented, was to execute an update before every insert:
Update Table1 set PartnerRef = PartnerRef + '_' + Cast(UniqueId as varchar(10)) where [email protected]; Insert new record using @partnerRef;
So, the value of the field in a previous/existing record is changed and then the new record is created. The old record cannot be deleted so soon, but also the original value in the old record is not that important anymore after being reused, so I can modify it without any problem. The update query runs before every new insert, but most of the times(90% maybe) it does not change anything, a previous record with the same PartnerRef does not exist. Leaving aside the stupidity of the whole table structure situation, my actual question is about performance and optimization of the update process. Would there be any advantage from using an IF EXISTS UPDATE instead of the simple update?
IF EXISTS (SELECT 1 FROM Table1 WHERE [email protected]) Update Table1 set PartnerRef = PartnerRef + '_' + Cast(UniqueId as varchar(10)) where [email protected];
Is there a better/quicker way to do this update, until we change the web application and remove the unique index to allow duplicates in PartnerRef? Thank you for any idea.