neeraj.it (4/25/2013)
Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.
Thanks
But now your system is operating without a safety net. You could be getting dirty data. In fact, as data increases the chances of having it dirty increase pretty radically too. Instead of just dropping constraints, you should have identified what was causing the poor performance directly (scans because you needed an index or something along those lines).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning