I hit one during the like ten days I played play money limit hold em games, right before I started playing for real. I have had two in real money games. One of the two came on the river after I was all-in on the turn (and was already ahead on the turn), the other came on the flop and I made no money on it other than from the preflop limpers. Pretty disappointing results actually.
My best result ever in a big hand (in terms of BBs in one pot) was a straight flush at 1c/2c NL, where I hit the straight flush on the turn and 5 players got all-in on the turn. $10.00 or so pot felt huge at the time.