We had 3 border collies when I was growing up in Wales.
My experience with them has been fantastic- the first came from a local farm and was my 8th birthday present and was probably my best friend growing up in a very remote rural area. He was affectionate, daft as a brush, and fiercely loyal. I still miss him.
A couple of years later we added a white BC bitch from another local farm. She was probably the most intelligent dog I've ever known - a proper character with real problem solving skills (she habitually waited for my mum to go bed before jumping up on the sofa and plumping up the cushions to make a cosy nest for herself, and would put them back where she found them if she heard my mum coming down the stairs - my mum was none the wiser for years!).
Last one came along a couple of years later as a stray we adopted after one of our neighbours found her cowering in the back of his truck after delivering to a hill farm in Snowdonia. She was probably a couple of years old and had clearly had a litter of pups already. She'd been badly mistreated and didn't trust humans at all. She wasn't houstrained and was horribly incontinent due to being so nervous around people. My dad took her in and fed her up and after about 6 months she was a completely different dog - she was a long haired tri-colour so a beautiful dog, incredibly placid and loving. She had a litter of lovely pups sired by our eldest dog which all went on to good homes locally.
They do need plenty of exercise but not as much as say a Springer. They love mental stimulation and problem solving and as already mentioned that's probably equally important as exercise - hence why they're used a lot for agility trials and the like. Ours were happy to be left for a few hours but there were three of them so they kept each other company - the eldest was a bit chewy when he was on his own and got himself in a few scrapes until the others came along.
Ours loved the water and would happily spend hours tearing along the beach / into the water after a tennis ball which my Dad used to hoof out with a raquet.
Only possible downside is that they are very much pack animals and can be fiercely protective of family members (I read somwewhere that genetically Border Collies are closer to wild dogs than other breeds...not sure how true that was?) - our eldest would bare his teeth and raise his hackles at anyone he thought was likely to harm us (raised hands etc) and had a big problem with authority figures - basically he'd go mental barking at anyone in a uniform - but he never went for anyone.
We also had to carefully train ours to behave around animals - as above their instinct is to round up anything that has 4 legs or wings. In fact our eldest in his younger dafter days would also try to herd motor vehicles until he came off the worst in a tussle with a mark III Cortina... he was fine afterwards apart from a bit of a limp which would be strategially deployed for sympathy / treats. Ours were all eventually fine after training and would happily walk to heel around sheep.