What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?
Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to


Choose (and document) what it means for each nullable field
Make sure it means one thing only


With those rules, consider the following use case:


You have a table 'shop' (eg individual premises)
You have a table 'retailer' (eg chains)
The 'shop' table has a lookup field referring to the key of 'retailer'
You have defined null to represent an independent shop (ie one that is  not part of a chain)
A 'retailer' closes branches to the point that you consider its shops to be independent


In this case, an on delete set null makes sense. There are other ways of modelling these business rules but this is the simplest and if it accurately fits the facts that you care about in the real world, I suggest it is perfectly ok