January 18, 2015 at 8:02 am
Comments posted to this topic are about the item Untrusted Foreign Keys with NOT FOR REPLICATION Option on the Key
January 19, 2015 at 7:56 am
Hi Brian,
I assume you are using transactional database as an example, but I would like to hear your thoughts on ROLAP structure. There is an opinion among Data Mart developers that FK constraints are not necessary and since integrity is normally checked during ETL process, they could (some developers say should) not be created at all.
I have created them deliberately in is_disabled/is_not_trusted form for declaration purposes only, so relationships between tables could be used by some BI tools to automatically load semantic layer and by modeling tools in case of reverse engineering would be needed.
It seems you have spent some time investigating the execution plans with trusted and not trusted FK constraints, so what would you comment on that?
Sorry for slight off-topic question.
January 19, 2015 at 9:31 am
Hello Roman,
Yes, my database environment is OLTP. Unfortunately, I don't have the expertise to answer your questions regarding ROLAP since we don't do Data Warehousing or Data Mart here.
I understand your developers are trying to speed up the system by eliminating the relational part of the relational database, but I've heard too many stories of referential integrity problems when the integrity was handle at the app level that I just don't trust them. But it's your data. Maybe this time it's different.
As for Foreign Keys and performance, a case can be made for having them since there are cases where having a Trusted Key Constraint can improve performance by eliminating the need to even read a table as part of a query. Here is an admittedly contrived example that shows the effect:
If the foreign keys are disabled or not trusted, this type of optimization is not possible.
As with everything else in SQL Server, the real answer depends upon your application and should be tested in an environment similar to your own.
January 21, 2015 at 12:51 pm
Add this to your 1st query and you can also list the table name in case the FK is not named well
,OBJECT_NAME(i.parent_object_id) AS [Table Name]
January 21, 2015 at 12:56 pm
Thank you. That's a good addition. In my case the foreign keys were named well, but that isn't always true. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply