The real benefit of water is as the smoker goes above the boiling point (212f) the water pretty aggressively "sucks" extra energy to boil water. This makes water a pretty fool proof way of keeping temps down around 212f - so if you're new to smoking and vent control, you still turn out a decent product since the smoker is going to hugely resist rising temps. There is also a bit more humidity, but tons of debate whether or not that makes any difference at all.
The downside is that extra energy results in way more charcoal usage. And this can be a pretty serious downside. You can run out of fuel during an overnight cook, and in general it's just not efficient.
Sand (PLAY SAND not construction sand) is fine to use but if grease gets into it, it can go rancid. I prefer the ceramic briquettes, specifically charbroil makes cheap ones called char-diamonds.
Sand or ceramics will help buffer temp swings - if the wind blows into the grill or if you remove the lid, the temp isn't going to jump around as much and will recover much quicker. In a perfect "lab-ish" environment there isn't much benefit to mass inside the smoker, but in the real world it does help, in my opinion.
I don't use terra cotta as it's possible (unlikely) that they could contain lead. The actual risk of this is very very low, but ceramic briquettes last forever, don't crack, can be put in a 650f grill to clean them,... I just don't see a benefit of terra cotta over these ceramic briquettes.
If you check amazon, there's a person who sells 1" deep pans that fit into the water pan. I put in some ceramic briquettes then add the foil lined pan. That way, even if grease gets into the pan, it's super easy to wipe out.