With no modifications to my phone, looks like all sites that detect the browser identity, have and alternate Mobile view and auto switch to the appropriate view DO switch to mobile view automatically irrespective of the Opera option "Mobile view" checked or unchecked on my wife's and my Sprint Touch Diamond. As an example these sites will ONLY show in Mobile view unless the site has a link to switch to full desktop view.
www.bankofamerica.com
live.com
qik.com
www.ustream.tv
spaces.live.com
Checked or unchecked, its the same result.
After testing this, I installed the Opera Mobile 9 beta and it actually respects the Opera "Mobile view" setting, but could only use the soft full "Keyboard" which looks very ugly and is extremely difficult.
I then installed another Oper 8.x.x (I can't remember which ver) that respected the Mobile view setting, allowed the use of the Phone keypad, Compact and Full QWERTY, but got an opera appliction error at startup (even though everything loaded fine).