It is an exhaust with the Catalyst removed. It improves flow rate but most cars will fail the UK emissions test if you take out the cat.
It could be a long length of exhaust or just a small pipe which replaces the cat.
Some cars have a smaller pre cat and then a larger cat further down in the exhaust.
A sports cat is a good compromise. I know some drivers who have a pipe they bolt in for off road use to replace the cat and then when they need the car to be legal they just bolt the cat back on.