(R)aid0, there is no redundancy. so AID0(Array of Inexpensive Disks) Is best used where you need top read/write speeds.
Video editing with HD or uncompressed video will see a benefit since the data rates are higher. It is also faster to look through frames in an editor on a (R)aid0 system.
Games that are not heavily compressed also benefit from (R)aid0.
If a game uses heavy compression then the cpu limits the speed at witch data can be loaded and a faster hard drive does not help.
Games with little compression will load faster off a (R)aid0 setup.
Windows generally runs a bit faster and smoother on a (R)aid0 setup.
What does (R)aid0 not help?
Random small read/writes(the they are smaller then the stripe size, they only write to a single drive so there is no speed boost) and situations where an application needs to work with or decompress the data and is limited by the speed of the cpu.
If you use (R)aid0 on windows and one drive fails then yes ALL data is gone, but 90% of the time you will get smart errors reported before it gets this bad. This gives time to get your data off the drive.
I personally use (R)aid0 with 3 x 640 gig WDC blacks for windows and all apps/games. I store files on 2 additional drives(Files and Videos) since they do not need speed and I have a external backup as well for all my files.
One day one of my 640 gig drives started to die, but i was warned in advanced by Intels software(and before loaded the OS) and I was able to get all my needed files from the array. Raid1 while slower would have had no need to backup(see note below), just replace the drive and rebuild.
Since SSD's are not asked in the OP, i will not go into that.
Note: By no need to backup I mean files will not be lost. Important stuff should ALWAYS be backed up in case of on foreseen(i want to say unforeseen consequences, but will not
) deletion from mistake or virus attack.