I tend to prefer English suffixes and camelcase rather than Hungarian prefixes and underscores. Like, for a steel coil, InnerDiameterMM, MassTonnes, GaugeMM, etc. I prefer to leave abbreviations for cases where it's obvious even to non-experts, like mm for millimeters.
My only issue with that is you'd wind up with some very long column names.
Using prefixes you can encode a lot of information in a relatively short ID. Which may or may not be useful. As you mentioned though the downside is it requires some domain knowledge to decode.
A lot of our columns are in a form like this:
TP_TSC_HEX1_XXXXX
(where XXXX is some ID usually from scada system)
I can see from the name this is a temperature corresponding to thermocouple id: XXXX in TSC Heat exchanger 1.
Edit: I should also mention some programming languages (not sql) have a language feature for specifying named ranges (I think it is called lazy evaluation) which tends to favor prefixed names over suffixed - I.e x1,x2,...,xn to iterate over the prefixed range. We use SAS heavily at my work which supports using this syntax, which probably influenced the design of our DB tables.