This usually allows manufacturers to create external drives that can be hot-swapped (via USB) more easily than with IDE drives. However, this usually also works for internal drives.
If you have an internal SATA drive (hard-drive, DVD drive, etc.) which is not connected to the motherboard and boot up Windows, not surprisingly, it will not show up in the Device Manager. If you then connect the drive’s data cable to the motherboard (and perhaps optionally) do a re-scan in Device Manager, the drive will then show up because the SATA controller detected the drive and made it visible to Windows.
This is great. If you accidentally forget to connect a SATA drive to the system and don’t want to shut down or want to create your own sort of external drive, this feature is very helpful.
Unfortunately, like most things, the pros come with cons. In this case, while the newly connected drive becomes visible to Windows and is usable, it will likely have poor performance. Windows loads the drivers for the drive on boot, so if you connect the drive after booting, instead of loading the tuned, high-performance drivers for it, it will end up using simple, low-speed access to (i.e., it will not use UDMA). You can see this as extremely slow transfers to/from the drive. If you do this with a DVD burner for example, it will be limited to ~2x even if you set it burn at 8x or whatever). You can also see this effect in the Task Manager’s Performance tab; the CPU load will jump when you use the drive, but no processes will spike because the load is coming from the kernel. This may or may not also occur in Linux or Mac.
So while SATA allows hotplugging drives, it doesn’t mean it will work well. Manufacturers will still need to provide a shim to allow for maximum performance and OS developers will need to better detect newly attached SATA drives and load the drivers at runtime.