Hi Guys

Thanks for your responses. I just wanted to clarify my position

So i started here 3 weeks ago on a system thats been developed in c# and t-sql(supposedly agile but not!). Its been pretty poorly architected and they did not take partitioning into consideration until now. So they have now decided we need to partition which is great. So on a lot of the tables when we implement partitioning new appropriate partition key columns need to be added to a number of the

tables as the developers thought using char for date columns was a good idea! So this new partition key column will then need to be added to any child tables that this table is referenced by and this has a knock on affect on development time which the project cant really afford.

So I was asked by the architect to run a test where we replace all foreign keys with triggers to enforce referential integrity. Collect perfmon stats and then management will decide what the best course to take. The big thing for them is not slipping on the go live date and they are very much in love with the trigger idea.

I obviously want to maintain referential integrity because I dont want to be doing a cleanup in the future. My issue I guess is more about navigating the politics of the organisation. Is the trigger idea an acceptable compromise?

Grant with regard to the deadlocking the resource thats causing the contention is a primary key on a table that is constantly being updated while a table that references is also continually being inserted to and my thoughts are that the FK constraint isnt helping. Im still at the point with deadlocks and lock escalation that Im not entirely sure because mostly dealocks just give me a headache and make me want to take a coffee break.The fix is an architecural one. I have recommended that they use SSIS to do de-duping and bulk inserts into the insert table and move the field in the parent table to another table. Still learnng in this area though!

Sorry bit of a ramble

Have a great day

Chris