So far I've only been to Mildert's (obviously), Aidan's and Hatfield's.
I'd like to think I'm being fairly objective when I say that my college's ones are the best out of those! Mildert has the biggest dining hall in Durham, and when it's at full capacity (which is quite often) and everyone's in suit/dress having a merry ol' time, the atmosphere is pretty special. Sometimes they get a bit rowdy, but there are no fines and general conduct is enjoyable for all. We're allowed 1 bottle of wine each purchased from the bar, and drinking games really depend on your company.
As for the others:
Pros - They have fancy dress, themed formals a certain number of times a year which I like the idea of - they probably spice things up a bit; also I got the impression they were quite liberal.
Cons - Hall is nothing to rave about.
Pros - very unique in terms of their traditions which makes it interesting; you can buy your own wine (which means you could get cheaper wine than bar prices).
Cons - their uniqueness was for me, novelty factor only* - I think it would get boring if I went to Hatfield formals regularly; really harsh fining goes on; too much emphasis on 'the' drinking game - and too many rules for it too... dunno, maybe it was the company.
*I say this particularly in reference to the 'spooning' ritual which went on, rather repetitively for about 20 minutes =/ .