I don't know if "conscious sedation" is universally used for colonoscopies, but I've had it (for an upper endoscopy) in the past, and I found it interesting.
The really odd thing about conscious sedation, for me, was the amnesia. Most of us have seen television shows or read books about people experiencing a loss or lack of memory of events, but it's another thing entirely to experience it, and my upper endoscopy some years back was my first such experience.
I do remember being wheeled into the room for the procedure, the IV starting, and then....
...I was asking "did they find anything?"
"No," the nurse replied kindly, "did you know this was the fourth time you've asked?"
I didn't. I noticed I was lying in bed in the recovery room, and ... hey, how did I get dressed?
It is a curious thing, and it's difficult for me to know how much of this feature of conscious sedation is keeping the patient from the memory of the pain or discomfort of the procedure, and how much of it is just a side-effect of an otherwise effective medication. I don't know the answer, I'm curious to know the answer, anyone know?