I have a Riff Raff in an SG and I'd say it's clear, but not especially bright.
If you imagine a "typical" PAF-type tone (I know there are lots of PAF variations, but I guess you know roughly what I mean), the Riff Raff is just a bit drier and grittier, still versatile but a little bit more of a "rock" pickup.
It's not a knock-your-socks-off sound where you notice a huge difference straight away, you have to spend a bit of time playing with it to see what it's capable of. And it's great for 70s hard rock tones.
My SG only has one pickup, but I think the RR would be great with a sweet-toned neck pickup like the Stormy Monday, as Ben suggested.