It isn't scary. Like any job you get used to things. You learn early through training and other officers what to expect. You will see and do things you didn't expect to do but you become prepared for that. There is a lot of pressure and you take things home with you. It can be difficult to leave bad things at work. You have to learn different ways to cope with that. Police Departments are better today at helping individual detectives deal with stresses. It isn't so much scary as it is difficult to see some of the bad things and having to deal with families and people at the worst moments in their lives while you continue to do your job. Best of luck to you. I have bee a detective for over 17 years and a police officer for 28 years. I have found my job difficult but very rewarding. I would never have wanted to do anything else.
The short answer is: It depends. There are various types of detectives. Some investigate property crimes while others investigate persons crimes. Then there are specialties like finance crimes and child abuse.
I was never afraid as a burglary detective and I will tell you that was the most fun. It is the last true detective. There are no built in suspects and one has to actually investigate. However, as a child abuse detective within the Major Crimes unit there were times I wasn't comfortable. But being afraid sometimes comes with the job. It is not the fear, but what you do when afraid. The welfare of others depends on you.
This leads to the fear of failure. You fail and a bad-guy goes free and if you fail badly, then by law you can not prosecute the individual in the future either.
It's a good position and a good job... nothing like what is seen on TV. Hope that helps.