Generally, those who don't want it do, while those who do don't.
The lethal "non lethal" brawls as well as alcohol poisoning deaths are caused by the serving of alcohol in the tavern, which happens if the tavern is staffed with tavern keepers or performers (there doesn't seem to be any difference between the two: they serve about as much booze, and they perform about as much).
Thus, you'll get rid of those problems (if you're among those considering it to be a problem, which seems to be the case) by removing all personnel from the tavern. Visitors will still come (and often overstay their welcome) as long as the tavern is open to visitors even if there is no booze serving.
The effects you won't get from not serving booze are:
- Booze fueled bravery is negated, so your dorfs won't run around kicking the crap out of the local wildlife (as much).
- Visitite mining grinds to a halt. It's said goblins are particularly vulnerable to booze...
- Your local vampires won't drink booze (which they'll do if served), and thus will suffer from booze withdrawal slowdown.
- Getting the tavern floor painted in lovely colors of green and brown from vomit and spilled booze respectively (although dorfs tend to clean that up unless it's outdoors).