IME, the short answer is, "Yes, they can be beneficial"
I've been using a Hydor rotating nozzle, attached to a 210 gph Tunze Silence return pump on a 12g near-cube aquarium for 12 years. Due to the tank's rounded corners and the 'scape in the center, it's been fine for a mixed reef with stony corals like Pavona, Montipora, Seriatopora, etc.
FWIW - For about 1/2 year I experimented with straight flow out of the return which resulted in ~25% more flow. However, the results obtained were mediocre and not nearly as good as the chaotic flow patterns created when using the Hydor, even though flow is somewhat reduced when using these rotating nozzle devices.