For most people (those without ADHD), snorting methylphenidate is very stimulating. It's considered a recreational "upper" and has effects comparable, broadly, to cocaine or methamphetamine. Mental stimulation, increased energy, euphoria. Many students use it as a study aid, especially to pull all-nighters.
When you say other drugs people use to get high... do you mean pharmaceuticals? benzodiazepines are very popular: Xanax, klonopin, Ativan, etc. These give a sedating high and usually compromise memory and inhibitions as well.
I mean, there are a ton of pharms people use to get high. Any specific questions?