Technically, SF Bay is full of water. Though the bits I lived near were mostly salt ponds, which are not exactly a gorgeous view - though it might be a matter of taste; I rather enjoyed the wildlife refuge on the east end of the Dumbarton bridge. And the bay is flat with no waves and few points of interest. But you could kayak in it.
If you want water to look at SF offers many pretty vistas and has the virtue of being the local nightlife. Commute to Silicon Valley is nasty but you'll at least have lots of company.
If I thought I loved Half Moon Bay so much that I wanted to try living there, here is what I would do: Rent some temp housing next door to work. Then, every day after work, drive to Half Moon Bay. Spend a few hours picnicing, admiring the water, etc and then drive home. That experience will be just like living there, except you'll only fight rush hour once instead of twice per day and your kitchen and bed will be on the wrong side of the mountains. After a month of that, plus some local research and side trips, you'll be able to answer your own question.
