Will changing the HBOOT affect my ROM? Device: HTC Desire (Bravo)
ROM: Aurora 4.1 - http://android.elrincondelmovil.com/roms-desire-90/rom-aurora-v4-1-sense-3-0-android-2-3-5-oct22-1639/?langid=1
HBOOT: Stock (Revolutionary)
Radio: 5.11.05.27


I'm looking to upgrade my ROM to Tiny ICS which like many ROMs claims to run smoother on the CM7r2 HBOOT. So my questions are:


Is it really worth changing my HBOOT?
Will changing my HBOOT wreck my current ROM?


Sorry if this is somewhat 'noobish' but I just felt it would be better to be safe than sorry.
This answer to #2 in your question is, unfortunately, "maybe". The CM7r2 HBOOT (along with several others) is maintained by AlphaRev, and they note in their website's FAQ:


  Will this touch my currently running ROM?
  
  We try to leave the current ROM and datastructures completely intact.
  It is, however, advised to ALWAYS nandroid backup your phone before
  you run this procedure.  You will then always have a correct/current
  backup to fall back on in case something breaks.


So it's designed to flash without affecting your current ROM install, but it's a bit of a delicate process so there are no guarantees. There is generally always a certain amount of risk involved in these types of things, but especially so when dealing with the bootloader/HBOOT. Make a backup. If you can, copy the backup onto another device (your computer, for instance). Always prepare for the worst and hope for the best when it comes to data that you don't want to lose and you'll generally be fine.

As per your question about whether or not it's worth it - this sort of depends. The reason that some ROMs support certain versions of HBOOT and not others is because the different HBOOTs are designed to create different partition layouts on your device. There is a table on the website which details the layouts, and which I'll summarize here:

Name               /system   /cache    /data    
---------------------------------------------------
Bravo Stock         250M       40M      147M    
Bravo N1Table       145M       95M      197M    
Bravo Data++        180M       5M       252M
Bravo Sense         180M       40M      217M    
Bravo Oxygen r2     100M       5M       332M    
Bravo CM7 r2        145M       5M       287M


Certain ROMs are designed to take advantage of the differing partition layouts, making them incompatible with others. For example, a ROM with a lot of stock components and additional packages may need more than 145MB of space for its /system partition, so flashing it on the CM7r2 HBOOT would not work. It's generally best to stick with whatever the ROM author recommends, which does mean that switching may be necessary in some cases.