I owned a C-64 but I was real young and more into games and the like. I keep hearing and seeing ppl. go head over heals about this chip but I never quite understood why it was so great.
I had a disk of SID music way back then so I know it plays music and stuff, but it only reminded me of .MOD music...
By "MOD" music I assume you mean multi-channel sound...
SID was pretty cool. It has 3 sound channels/voice each of which had various waveforms you could pick from(including sine, square, random etc). Those sounds where then altered by the envelope generator which would basically control the volume. By setting the waveform and the envelope generator for various timings of Attack, Delay, Sustain, Release intervals you could make a sound resembling a musical instrument, or alternately, make something sounding really unique/strange/funky. On top of that there are sound filters and I forget what else.
Most common computers at the time(excluding Atari which I can't speak for) had less sophisticated sound generators and only had a single channel/voice.
I do think the SID sounds very unique/distinct, but I'm also partial to the Roland MT32 for the same reason.