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..
SELECT CAST(GETDATE() AS NUMERIC(38,2)) [Test1]
Result:
SELECT CAST(GETDATE() AS DECIMAL(24,9)) [Test2]
Result:
And when we union them together:
SELECT CAST(GETDATE() AS NUMERIC(38,2)) [Test3] UNION ALL SELECT CAST(GETDATE() AS DECIMAL(24,9)) [Test3]
Result:
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.