Passive pickups are your standard run of the mill invented in 1930's pickups (obviously there's been a bit of progression since then in pickup technology but the underlying technology remains the same), Active pickups are, in basic terms, a passive pickup with a preamp built in that amplifies the signal coming out of the guitar slightly which generally cuts the work of the amp down by a bit and theoretically allows clearer tone. However a more expensive pickup will probably have as good tone anyway.

Metallica and many other heavy metal artists, most notably those using ESP's, are heavy users of active pickups. Active pickups perform very well with distortion. That's about all I know on the subject. Despite the nature of active pickups being powered they actually work better close to the string (strangely), I have no idea why. Great for heavy metal and hard rock but not very versatile!