It takes several seconds for a hard drive to spin up or spin down. The normal operating speed for most hard drives is 7200rpm, and they don't get up to that speed instantaneously.
So yes, hard drives normally spin for as long as the power is applied.
There are two exceptions to this general rule:
1) "Green" drives are specifically designed to use less power, and most of them automatically spin down after several seconds of inactivity and then spin back up again when accessed. These drives usually spin at a slower rate (around 5400rpm) and will obviously cause a pause in your system if you try to access them when they're spun down.
2) Windows itself has power-saving features that can automatically spin down hard drives after a certain period of inactivity. You use Start -> Control Panel -> Power Options to configure that.