The operator $a_p$ is an annihilator: on a state with momentum $p$ it gives the vacuum,
$$ a_p \left| p\right> = \left|0\right>,$$
but on any other state it gives zero. (N.B. the vacuum state is quite different from the number zero!)
The operator $a_p^\dagger$ is a creator: on a state without a particle with momentum $p$, such as the vacuum, it creates one:
$$ a_p^\dagger \left| 0 \right> = \left| p \right>.$$
What the creation operator does if there's already a particle with $p$ depends on what type of particles you're talking about. For fermions you're not allowed to have two particles in the same state, so $a_p^\dagger\left|p\right>=0$ (though you might properly need to specify some other quantum numbers, too). For bosons you may have multiple particles in the same state e.g. in lasers or condensates, so $a_p^\dagger \left|p\right>$ would not vanish.
This means that the product operator $a_p^\dagger a_p$ that it vanishes when acting on states which contain no particles with momentum $p$,
$$
a_p^\dagger a_p \left| q \right> = 0,
$$
but has eigenvalue 1 on states which contain particles with momentum $p$,
$$
a_p^\dagger a_p \left| p \right> = \left| p \right>.
$$
That's essentially what a Dirac delta function does. Your number operator,
$$ N = \int \frac{d^3 p}{(2\pi)^3} \, a^{\dagger}_pa_p,$$
integrates over all momenta; it will leave the state unchanged, but pull out one term for every particle in the state.
You can be more explicit using commutators, as shown by Phoenix87.