Yeah, the Opie scene was so violent too. Broke my heart.
For me the LOST death that had the biggest impact was Charlie. It was a Holy Shit moment that is rarely repeated.
Another example I can think of is on Sons of Anarchy when Opie is killed (season 5). I didn't sleep that night. His face as he fell was so haunting!
How about on Scrubs when Laverne gets in the car accident and the staff has all episode to say goodbye? That was a real tearjerker.
Sookie's Gran on True Blood hit me kinda hard, especially with her being a secondary character, I had kinda felt her to be safe: a little old lady.
I know you didn't ask for it, but they most satisfying character death on TV was Rita on Dexter (season 3 or 4). I cheered when she was killed off. [Runner up was Lori on Walking Dead]