It can happen in some games, it varies. The Assassin's Creed games don't handle Vsync well and suffer from some pretty bad stuttering when it is enabled. The vsync option in the Dead Space games is completely broken, resulting in a framerate cap of 30FPS, and lot of mouse lag. In Crysis, Vsync drops your framerate to 25FPS in most areas unless you have a GPU that can deliver at least 50FPS consistently, which on the very high settings is still pretty tough to do.
Most other game I've played haven't had any issues with vsync. If you want to get rid of screen tearing, that is pretty much the only way to do it.