Just an example, my gamecube and original xbox (all connected via s-video) look fine on my LG L32C2D 1366*768 LCDTV, and terrible on my 1680*1050 Dell 2007WFP. View distance comes into it a bit too, but I'm pretty sure the LCDTV has a better upscaler.
Having said that, DVDs on my 360 (connected to the LCDTV) look better than with my non-upscaling Philips DVD player (upscaled by the LCDTV), so that means the 360 has a better upscaler than my LCDTV, which is why I'm going to buy an upscaling DVD player.
SD on a 1080p LCDTV, it will probably look a bit dissapointing to start with because LCDs show up the compression on digital channels, and most digital channels just don't have enough bandwidth for good picture quality. You do get used to it though, and the channels with good signals still look good. To get the best out of the set though, obviously HD is the way forward.
I'm assuming you're talking about a 1080p LCDTV and not a 1080p TFT monitor, which would be a lot worse. I've also tried Sky digital on my Dell and it was again pretty nasty at PC viewing distance, and still didn't look great even at distance.