It depends on what kind of material you are sanding, and what you are trying to achieve. On wood or aluminium, sandpaper up to 320-400 grit still noticeably remove some material. 600 to 1000 will start to yield a smooth surface. Above 1000, you're basically removing no material at all, mostly polishing.
Keep in mind that it is best to use grits in sequence, so that the next (finer) grit of sandpaper removes the sanding marks of the previous grit. If you use 320 grit on aluminium and then switch to 1200, it will take you more time and effort to get a smooth/polished surface than if you use 320-400-600-800-1200.
Water acts as a lubricant and makes it easier to sand and avoid big sanding scratches.