Okay, so I don't know from first hand experience since I am neither an NP or CNS, but I have second hand experience lol. NP's are in direct patient care, prescribing, diagnosing, treating (as state laws dictate), and their function can range depending on specialty: FNP primarily outpatient/clinic/primary care: acute NP's obviously practice in a hospital setting with varying hours.
CNS: have a wide variety of functions, but do not perform direct patient care, generally speaking. They are the "kitchen sink" of advanced practice nurses. They perform research to help implement evidence based practice, improve patient outcomes, educate staff nurses, staff development. They generally work in a hospital setting as a CNS on a specific unit. We have a CNS in our ICU, and she does the things I mentioned above. I imagine that function varies depending on hospital setting.
I would think your career options would be more limited with the CNS route as opposed to the NP route. More jobs available for NP than CNS. There are only so many CNS in a hospital system, and once they get a position, they generally don't leave. You probably could get a job, but it might require a move. Your job market would vary with NP, too, since NP's might saturate certain areas, but the need is greater for NP's overall.
Best of luck!! I'm looking into going back to school as well, just not sure when I'm going to take the plunge
. You should get some good info on the site. I've read some old threads on this topic before, as well.