For Civ 4 at least, once you have cavalry, cannons, artillery, etc. There is a penalty for death stacks due to collateral damage.