Never come across this before. I've always moaned about people using VARCHAR without a SIZE, but in practice the chances of a side effect on that are rare (can't remember when SQL decides that VARCHAR(1) is the sensible default, but when folk for CONVERT(varchar, @MyNumeric) the default is big enough ...
... but ...
I had never realised with DECIMAL that the default SCALE is 0 ... the consequences of that are dire (unless the value is an INT but then WTF would I be using decimal?
)
I continue to find it staggering that STRICT is not yet available. I don't use a SQL Lint routinely (and not sure I would bother to have done so in this instance, as I would not have thought it would be a coding issue). [Just tried SQL Enlight on this - it failed to raise an alert
)
The situation was:
VIEW that maps Remote Table column names to Local Table's names, and aligning some other things like COLLATION
2nd VIEW that is used specifically for Import which does a few other things (such as treating blank strings as NULL, and a few conversion-y things)
Then a local Table which receives the imported data
The Importer SQL has a Temporary table which does things like VARCHAR(8000) and Nvarchar(4000) to then be able to check that incoming data has not exceeded the local table's column width (e.g. an upgrade on the Remote System has widened a column and All Hell Breaking Loose needs avoiding ... and thus the Importer is designed to be the First Responder
)
All of which disguised the problem because it was the #TEMP table which was wrongly defined, so I am looking at Remote Table having a correct value, all the VIEWs on it likewise were correct but then the actual imported table's data was wrong.
Further compounded because most of the figures in the remote data are not what the client bills out anyway, they separately multiple Hours by Rate and round UP, and throw away the Value that the remote is holding ... which only leaves a few Expenses items which are not rounded and the original is preserved exactly as was. With all the Rounding Up I was looking in the wrong place for why the Expenses were being rounded, - when they shouldn't have been