Sql Server: What is the benefit of using "Enforce foreign key constraint" when it's set to "NO"?

In normal production, this setting should never be set to NO.

But: when you're developing, or restructuring a database, or when you do e.g. a large bulk load of data that you'll need to "sanitize" (clean up), then it can make sense to turn off foreign key constraints to allow "non-valid" data to be loaded into a table. Of course, as I said - you shouldn't keep that setting turned off for a long period of time - you should then proceed to clean up the data, either delete those rows that are in violation of the FK constraint, or update their values so they match a parent row.

So again: in "normal" production mode, this setting should never be NO - but for specific tasks, it might help get the job done more easily. Use it with caution, and always turn the FK constraints back on as soon as you can!

Not in everyday usage, as far as I know. The times I've de-enforced foreign keys for a while are when there are problems with data and fixing them is hidered by relationship checks.

During bulk operations constraint checks are temporarily ignored in order to increase performance.

It can also be useful in a data warehouse staging environment where you don't want to enforce the constraints before you have processed / cleansed the data, but you still want to be able to use the FK relationships to understand how the tables link to each other. These relationships can be picked up and displayed in third party tools (e.g. Visio, ERWIN, etc), so it can be useful metadata even though not strictly enforced.