Everyone is different, and they can be effective or not. No clear answer.
My opinion is an ultimatum is really not much different than coming clean. It is simply me saying this is what I need. Period. They alcoholic abuses the relationship in so many ways and on so many levels, then we are told they have to do it on their own. That their recovery can only be successful when they choose to make it, on their terms, and we have no control over it. Fine.
However, setting a minimum standard of behavior you are willing to continue to suffer through, vocalizing it, and sticking to it is well within a reasonable course of action for someone who lives with an alcoholic.
In my particular case, the ultimatum worked. Finally. After years of trying everything else. I had the divorce papers filed and sitting on the table. I said, "you go to rehab today, and do what ever they say. You start working on getting well. These steps are no promise we'll stay married. But not going today is a 100% certain the divorce will be final in 60 days. There will be no second chance". Initially even this didn't work. When my daughters both independently threw in they would never speak to her again or allow contact with her eventual grandchildren, the clue bird came and landed on her shoulder. It was her defining moment. The paths were clearly set. And she could see where each one led. There was no yelling, no fussing. Just clear communication.
This ultimatum, combined with resolve to follow it through and her initial efforts for sobriety have resulted in a huge improvement in my life and my kids life. Still no promise of a fairy tale ending in our story.
But without this "ultimatum" all 4 of our lives would be more difficult.
So I say, YES. They can and do work. And I ask, what do you have to lose by setting one? Once you decide to leave if they can't get well, giving them one more last chance isn't really going to harm you. Perhaps people like us need to do this in order to know we've done everything within our power to honor our commitments. Then with things clearly spelled out in a simple "if, then, else" format our path is clear.