Hi all,
I noticed recently that our impresario and impresario_cci databases have a large number of foreign key and check constraints that aren't trusted. (See http://www.brentozar.com/blitz/foreign-key-trusted/ for more information.) This type of situation can make query plans less efficient.
Likewise, I noticed that the syntax in the UP_ENABLE_FOREIGN_KEYS stored procedure enables constraints, but does not force the database to validate them - which will result in a constraint left in an untrusted state. The syntax in the procedure is:
ALTER TABLE [table] CHECK CONSTRAINT ALL
However, the syntax to both enable and ensure the constraints are trusted is as follows:
ALTER TABLE [table] WITH CHECK CHECK CONSTRAINT ALL
That leads me to a question... Are constraints left untrusted for a particular reason? I ran a script to identify and fix the hundreds of constraints in this condition on our test database with success (except for 5 check constraints, likely due to bad data). I would like to resolve this in our live database, but wanted to double-check before doing so.
Thanks!David
David,
It's always nice to know that users are digging into SQL Server and their data! This is a legacy from the past that we're (slowly) working to get rid of. While the article is right that the query plans are not as optimal without this setting, it's really only limited types of queries that benefit and not the type that normally appear in a system such as ours. The post that was quoted in your posting explains what those types of queries are.
Hi Chuck,
Thank you for your reply! I agree with you that any performance issue would be very minimal; however, I failed to highlight my bigger concern, which is the fact that this method can result in data that violates the constraints. In resolving this on our test database, I found 5 foreign key constraints that are violated. For example, we have almost 15,000 T_CUSTOMER rows with a prefix value, but no corresponding value in TR_PREFIX. That shouldn't be the case and I suspect this occurred due to some past bulk load while the constraints were disabled.
Therefore, I'd still like to resolve this. Are there any reasons not to do this from a Tessitura perspective?
Thanks again,David
Yes you are right about the data integrity and that is why we are working to address these constraints in a systematic fashion. But we need to do it in a way that doesn't cause lots of errors for existing users who may have let bad data in. We can't always rely on sites having the expertise to clean up issues caused by data imports, etc.
I don't see any harm in enabling the CHECK flag on these constraints as you are working through your data.
Great, thanks Chuck - that is helpful. I appreciate and understand the difficulties in turning on the CHECK flag as a standard practice. Thanks again!