The only reason to use CO2 is if you have a heavily planted tank. Its not needed in tanks without plants or tanks with just a few plants/low light plants.
CO2 will lower the PH of your water; which is why it is turned off at night when plants are not using it, or it is hooked up to a PH controller that monitors the PH level and turns off the CO2 injection when the PH starts to drop.
CO2 is also used in calcium reactors for heavily stocked small polyp coral reef tanks.