I don't think that's at all what he was saying. Those two scenarios are at two very polar opposites of one continuum. If you have a big composite primary key, maybe thats a good argument for using a surrogate key (although I've worked with tables with 200M rows with composite keys with up to seven columns and haven't notice any performance problems). There are optimizations which can be done on integer columns which can't be done on varchar columns (e.g. radix sorts, etc) but in many many cases, adding a surrogate key doesn't net you much.

As with any advice you get on the internet, take it with a grain of salt and try to figure out what works best for you. As to the OP's question, I think the answer is resoundingly "no", you dont NEED a surrogate key in SQL to get decent performance in most scenarios.