Sometimes you want a stronger sense of immediacy, or you want the story to seem to unfold as it's happening. Maybe something is going to happen at the end that you don't want the characters to know about, or the narrator is going to die (which obviously you can't tell about in past tense). Then it's fine to use present tense. It's most often used along with first person. "I steal down the hall, avoiding the creaking floor boards. When I reach the door, I hold my breath as I turn the knob."
Just be sure you know WHY you're making that decision, and above all STAY IN THAT TENSE. No changing back and forth once you've decided.
Of course, there's also future tense, but I don't know of any books written in future. That would be . . . weird. Awkward. "He will caress her face with the back of his fingers. She'll lean into his touch." Yeah . . . don't use future.
This brings us to Point of View. You've got first, second, and third. First is the I/me/we perspective. We see this most often these days in Young Adult novels or Chick-Lit. It works best if you have only one POV character.
Third person is the most common because it works for multiple POVS. He/she/they. Most often each chapter is written in third person limited (no head hopping--only one POV character per chapter). There's also third person omniscient, where the narrator is not one of the characters, but an outside force who sees and knows every character's thoughts and feelings. This is NOT the same as head hopping and is tricky to do well, but when done well, can be really awesome.
Then, there's second person. You. This is the "Choose Your Own Adventure" POV. Outside of that genre, I don't recommend it.
So . . . questions? Have you written or read a book in any of the "uncommon" tenses or points of view? What effect did it have?