Whether it's power or noise you're after, the more of the original exhaust system you are replacing, the better the results will be.
A Cat-Back Exhaust system or half-exhaust system generally replaces the rear sections of the exhaust, leaving the original catalytic converters and front pipework in place. This has the advantage of replacing most of the exhaust pipework and all the silencers in the original system, giving you more noticeable improvements in sound. Because you're replacing big chunks of the original pipework with a better flowing aftermarket system, you will also get measurable power-gains. Leaving the original catalytic converter (or DPF, or GPF) in place means you don't have to worry about retuning the engine or passing the MOT.
A turbo-back or manifold-back exhaust system replaces the whole system minus the very front pipework. You get all of the advantages of a cat-back, but with the additional benefits of higher flowing front pipework and either upgraded sports-cats converters or de-cat pipework. This means that you're getting improved flow-rate all the way through the exhaust system without the original catalytic converter in place to restrict things. In combination with Stage 2 (or higher) software this can yield very significant power gains.