Sheldon's character is a bit complicated which is why he is one of my favs on TV at the moment, he is right up there with Niles Crane for being a brilliant but extremely awkward person. I also like Temperence Brennan...aka Bones, the forensic anthropologist.
What I have learned from watching Big Bang is that Sheldon's mother is a born again Christian who raised him in the church in East Texas. (Being from Lufkin TX I know exactly what that means. whoo boy!) Sheldon is very close to his mother even as an adult. I keep wondering how he ever left the nest for something as completely absurd as going to college to be a scientist. ahhaha! But he does want to please her and relies on her comfort, and would be devastated if she was disappointed in him. He also cannot cope with change very well at all. So for all these reasons, he probably has a bit of faith hardwired in him, more than he wants to admit...but he might also be hedging his bets against hell.
However studying physics has given him reason to doubt, and he does claim to have rejected religion in favor of things he can prove. So he probably claims to be an atheist most of the time...but only when his mom isn't around. It doesn't seem to be much dilemma for him, except for all his social weirdness, he does seem to accept and play along that his mother really wants him to be religious.