An interesting issue came up recently where a union was creating a rounding problem. One side of the UNION contained a field with data type NUMBERIC(38,2) and the other side, (the equivalent field) was DECIMAL(24,9). One field having the bigger precision (number of digits to the left of the decimal point) and the other have a bigger scale (number of digits to the right of the decimal point)..
Let’s see it in action..
And when we union them together:
The answer lies in the fact that SQL Server will preserve the biggest scale over the biggest precision. After all I’d be far more annoyed if I lost £1000 that .0120144414324p..
The solution to this problem is to ensure that you cast to the same data type in both of your queries to ensure you know the data types that will come out of the other end.