I have several friends in different computer science courses across the country. They contain varying levels of technicality and practicality. Whilst some universities strive to teach their students skills in packages, some simple languages, and networking skills, as well as some practical graphics package experience, some others prefer to teach the fundamentals behind these packages. Cambridge is definitely one of the latter, with most of the first year taken up learning about the fundamentals behind computer design, and learning a few simple languages. Most of the course is theoretical in nature, and whilst there are several practically useful languages taught, this is not a course through which you are going to learn how to make Jurassic Park dinosaurs using a Graphics Package - you are more likely to learn how to make the Graphics package itself!
There are obvious tradeoffs between doing a technical course such as this one. Whilst you have a very firm understanding of the theoretical aspects of computing, there is a whole world of practical knowledge that you have to acquire via your own methods. This boils down to saying that you will not be automatically employable at the end of the course, but that you will have to learn yourself some of the practical skills that some employers prefer. What you have from doing this course is the complete comfort in converting from language to language, in changing systems, and in learning new aspects of computing, because you understand the fundamental parts behind the computer, and understand the processes that are taking place.
This course is not light on mathematics, so it is very important that you are slightly interested or at least tolerant of a fairly high standard of mathematics. In your first year you have to do at least a quarter of your course in mathematics, and another natural science subject. In theory there are several to choose from, but in practise nearly everyone does Physics. Physics is considered the hardest work of the first year - even though half your course is still Computer Science, you end up spending most time on the Physics. I did Geology as my Natural Science option, which has given me a lot more time to concentrate on the Computer Science, and I should add that Geology is fun as a subsiduary subject - at least, more fun that Physics.
Certain aspects of the course that could irritate you are: 1) The Discrete Maths lecturer - this course is hard as it is, and the lecturer is a bit boring. 2) The distance to the Computer Labs - this is only a semi-problem, since if you are at one of the far-out colleges, chances are you are close to the labs, and if you aren't, then there are labs in the centre anyway. The main labs are only used for supervisions (once or twice a week), and are only 10-15 mins on a bike from the centre!
Other than the above, there really aren't a lot of problems with the course - it's a good course if you are interested in the theoretical aspects of Computer Science.
In terms of University Life - at Cambridge there is an enormous amount going on. One thing you should consider if you are a keen sportsperson is which college you apply to. The university teams are strong in almost every sport, but when it comes to playing inter-collegiate matches, unless you join one of more sporting colleges (St Johns, Trinity... check their websites) you could be disappointed by the standard. The music scene is vivid - there are literally hundreds of little orchestras and groups and concerts and the like - if you play anything and want to get involved, then the music here is brilliant.
Evening-time activities for the typical Computer Science student are not particularly enervating, so unless you really want to stay in trying to figure out how to crack into the central mail server :-) then you might want to socialise outside of your course. On the whole the Computer Science students (compscis) are very nice people, but just occasionally you find the odd one that.. well.. anyway.
The night-life in Cambridge is notoriously bad, but all things considered I don't think it deserves this record. In truth there aren't many places where there are nearly thirty students-only bars (the colleges) which in theory you are only allowed into if you're a member of that college, but in practise no-one cares. The prices are very good, and on the whole the bars are decent. The most popular is Emmanuel College Bar, so if you are considering choosing college based on the bar activities, I can recommend Emmanuel!
Oh, a few last details. If you're coming to Cambridge, you will almost certainly need a bike. In some subjects at some colleges you can get away with it, but it still can mean 30 min walks to lectures etc... And one final point, any keen badminton/chess/table football/rugby/hockey players, come to King's College. We need you!!! Good luck....