I just got a new HDTV (yay!) and I was wondering if people feel that buying the composite cable for Wii to play games in 480p is worth it or not.

Does it make a noticeable difference? Will it make a noticeable difference on a small HDTV (mine is 15") or is the difference only perceivable on larger screens?

actually, depending on how clued up and anal you are about the quality of the picture, the difference between composite video and component video is very large indeed. and there are evidently good reasons for it.
composite video (the format which is sent down most generic video connectors for consoles) sends both colour and sync/picture signals down the same 2 wires. this obviously leaves less bandwidth for clarity. so you get a rather fuzzy picture. not usually noticable on older crt tv's, but moreso on lcd's. component has 3 sets of wires, one for the colour red, one for the colour blue, and the green one is both for the colour green, and the sync/picture data. obviously in this scenario there's more bandwidth available, so a better clarity picture can be sent to the tv.
we use a projector here for everything tv. even our wii uses component, and nothing else. the difference is astonishing. granted we don't have a hdtv, but you will notice the same difference. playing a game using the composite signals, then play the same game (or look at the same picture) on the component signals, you will start noticing differences. the edges around boxes, characters are crisper, clearer, less fuzzy. text is more readable. the list goes on.
if you're on a hdtv, i would recommend nothing less than component inputs. since the wii doesn't have a hdmi output, component is the next best thing. it allows the wii to send over the best picture it can, and then your hdtv's inners will upscale the sd picture to hd, hopefully with minimal bleed.
in effect though, the smaller the screen, the less you will likely notice it. our projection screen goes up to around 100" diagonal. so of course picture abnormalities are noticable in an instant. but you will most likely appreciate the component signal on your small hdtv better than you will a composite signal. (do hdtv's still have composite inputs nowadays?).
my general rule is this. if your tv has an rgb/component input, and the device you want a picture from has an rgb/component output, use it! lol

there is a side note to the component output though. if you use the virtual console a lot, you'll find there's a handful of games on there that, bizarrely enough, don't support the component output. in other words, if you try and play these games on a component output, the picture will either be too big (tall) for the screen, or be "rolling" (this is where the number of horizontal lines in the signal is less than the number of horizontal lines on the tv, so the wii starts sending the next frame before the tv starts drawing the picture at the top again. it only affects the vc though, as most of the games are old, and were designed for 240 line tv's. the wii upscales most to 480, but some use a different number of lines, 256 or 276 i think, i can't remember of the top of my head.
just something to watch out for.
and yes, component signals are better for 16:9 widescreen tv's, since again there's more bandwidth on the cables, so you get a better picture using these.

naw, most vc games work. but when you go through the shop, scroll to the bottom of the description text for the game, and it'll say whether it works on component or not. sonic 3 isn't component compatible. we have another which isn't either. super mario land 3 i think, but don't quote me on that.