SUPER CID
Super CID "allows you to flash ANY ROM to the device regardless of the carrier" (taken straight from the S-OFF sticky). You can certainly S-OFF, install custom recovery, and begin flashing custom ROMs without Super CID, but you will be limited in your choice of ROMs to those that are the same carrier as you have
CID is the Country Identifier, a code which is stored on your phone and software uses to determine which country or region the phone is for. HTC updates, for example, use it to determine (among other things) which languages to install.
SuperCID is a special code, 11111111, which means that software from any region can be installed.
While SuperCID may seem handy, note that if you are only interested in debranding your phone, the OTA (over the air) updates which HTC sends will not be available for your phone, since there is no specific region defined. So, in such cases, you should choose a specific CID for your region.