I prefer BB in SPS tanks, and sand in LPS/softie tanks.
My 90-gallon SPS tank is mostly BB. It went through stages. When I first set it up it had 1" sand in the middle and 3-4" on either end for Jawfish burrows. The sandbed actually stayed fairly clean (which isn't tough to do with a young-ish sandbed) and fish that are constantly digging. I used Fighting Conchs, Nassarius Snails, and Cerith Snails to keep the sand clean. I also gravel vacuumed it to its full depth on every waterchange since it was not setup as a functioning DSB. After the first couple years the Jawfish were moved out to another tank, one got eaten by a coral too, so I removed most of the sand. 12 months ago I siphoned out most of it, and just has a dusting now. It would be fully BB if I ever bothered to siphon the last bits of sand out. There is maybe 1/8" covering 1/4 of the bottom of the tank and it is mostly in the back.
The bottom glass is almost entirely covered in coralline, so I do not find it unsightly. Albeit the tank is viewed from a couch and the bottom isn't particularly visible anyway. Indeed a brand new, shiny bottom glass is rather ugly in my opinion, and it does take time to grow in, but you need to wait for the corals to grow in to make the rest of the tank look nice anyway!
I have a wrasse in the tank, and he just slips in behind a small piece of rock that's on the bottom and sleeps between the rock and the glass.
Cleaning is no big deal, the small amount of detritus and other junk settles in two different spots. Occasionally when there is build up for some reason I just stick a rigid airline tube in the tank that has a piece of flexible airline tubing on it and siphon it out. The siphon has an "L" shape bent into it because one of the settle spots is under part of the rock structure, so the bend lets me siphon out from under there. It literally takes less than a minute to get the airline siphon, suck the gunk out, dump the little container, and put the siphon away. It takes less time than feeding the fish.
My 50-gallon LPS/Zoa tank has just under 1" of sand. I have a couple Fighting Conchs, some Cerith Snails, and some Nassarius Snails. They do a good job keep the sand clean. I also gravel vac the sand on every waterchange.
A properly maintained/functioning DSB should not be gravel vacuumed or disturbed. You can tell it is functioning properly if it
does not need vacuuming! If it is not a properly maintained/functioning DSB then there is no point in having the sand any deeper than 1". If the sand is only 1" then there is no harm in siphoning the sand to its full depth. If the sand is deeper than 1" and it is not a properly functioning DSB then it is likely doing more harm than good as it gathers detritus and nutrients that can't be exported since the critters needed to do this are not in the sand (the DSB critters!).
So, in my opinion, for best results...
A) Maintain a 1" or less sandbed and gravel vacuum it regularly.
B) Maintain a BB tank.
C) Maintain a properly functioning and well stocked DSB and do not vacuum it, ever. Also be careful to not stock critters that will kill your DSB (like sand-sifting starfish or fish).