I've spent most of my career as a CNS in several different hosptials. The actual job duties vary a little from position to position -- but the focus is usually on helping the staff nurses to provide the best care possible rather than providing primary care to patients and/or serving in a physician-extender role. The CNS role often involves some of the following specific activities:
1. Assuring that the latest practice advances get implemented in your hospital (researching them, creating policies, educating staff, etc.) Upgrading the care given in your department.
2. Establishing new programs and/or policies.
3. Staff development
4. Serving as a consultant to the staff to help them solve particularly challenging clinical problems
5. Working with staff members who need a little extra education/mentoring to help them "make it" on that unit
6. Assisting the management team to be sure that the department is in compliance with all regulations, codes, standards, etc.
7. Coordinating (initiating, participating in) any research being done in the department.
The above list was just off the top of my head. It's kind'a a "jack of all trades" advanced resource for a unit/hospital to have. One thing it is usually not is the same thing every day. The CNS is there to address whatever comes up and to help meet whatever needs that arise -- whether they be clinical, educational, research, or a little on the management side. It's rarely boring or routine.