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.