I am qualified to give you a response. As I live there. But only until May 6th and then I will be moving back to Canada.
Sheung Shui is very small. It is one of the smaller areas in Hong Kong. It has a few high rises, and everything else is low and spread out. It doesn't have that city core feel. Just a small town feel.
Check out how small it is and how much green is around it. Feel free to check out the surrounding areas on that map. To the north you have Lo Wu which is the entry to Shenzhen. China. So if you have a visa to go in, it is just above.
The weather according to my friends who live in the area, say it is a bit colder here in Winter, and a bit warmer here in Summer. Not sure why that is. I find the difference negligible in person but I personally think it is at least better air.
Travel wise, it is a bit far. It will take you 40-45 minutes to get to TST. But being the second stop for the train after Lo Wu, you will usually be able to find a seat unlike the later stops down the line. Furthermore, you don't have to change trains, you can just it all the way down. If you have to travel into the island, depending on where you go, the bus at Hung Hom is usually a good choice. It is cheaper and more convenient to just hop on when you need to get to say Causeway Bay. If you're out late at night and miss the trains, you will have to take the minibus. If you're in causeway bay, there's a minibus that goes to Sheung Shui from the goose neck bridge (translated from Chinese) and it runs all night. It will take over an hour to get home from there depending on how badly the driver breaks the speed limit.
If you're on the Kowloon side, there's one from Mong Kok. So you have to take the taxi there first. There are two minibus choices you can take, one is the green one from the corner of Mong Kok road and Sai Yeung Choi Street I think. The red one I don't quite remember where exactly. The green one has a more direct route to Sheung Shui but the line up is horrible. The red one takes a slight detour but the line up is a lot nicer, so my friend and I usually take the red. The ride itself will usually take 40 - 60 minutes or so depending on how many people get off at wierd stops and how fast the driver drives.
All aforementioned Minibus has the final stop at Sheung Shui, at a spot near the Jockey club store which is located near the KCR station. You will not have to walk far to get home if you live in the Sheung Shui core.
Shopping wise, if you're in the core area, there's pretty much everything you need smacked into a small area, a wet market, wellcome, public library, etc etc. There are several small shopping complexes linked together where you can get the usual stuff. The malls are seldom crowded especially on weekdays. Weekends you will get folks from China and the surrounding areas, so it is busier. Retailers are not very well acquainted with English, but it will only be a problem if you want to ask a lot of questions. There are travel agents that speak good english at Wing On, so that's not a problem.
Food is a little bit more difficult. You do have the usual array of western food like McDonalds, KFC, DeliFrance, etc. There are some other nicer restaurants with pictures and english writing to be found in the shopping malls, and in the odd areas but the majority are local fare with chinese menus. If you often eat out then your choices of food will be a bit more limited. However you can always travel a bit south to Shatin and enjoy all the food there.
The rest is as HKChigger and dropdedfwed says, there aren't a lot of expats here or services catered for them. You will have the privilege of living in the real Hong Kong but in a much smaller, quieter, and cosy area. If you want to be in the thick of the western bubble to be found on the Island then this would not be a good place for you to be in. If you have kids, you may want to see if there are good international schools nearby as well. I don't know about those.
Good luck with your choice.
