For the rate, either DECIMAL(8,4) or DECIMAL(25,13) is good in this case; both give exactly the same results. But they still don't match the original test data - about 1 in 30 differ in the last digit.

At first it appeared that if I did the calculation in-line, the result was rounded, but if I did it in a function, it was truncated! I know there are issues with rounding vs. truncation - see this article - but the difference didn't make sense ... until I realised that some of the results were being multiplied by additional numbers. Here's some code that demonstrates the real problem:

IF OBJECT_ID (N'[dbo].TESTFunction', N'FN') IS NOT NULL DROP FUNCTION [dbo].[TESTFunction];

GO

CREATE FUNCTION [dbo].TESTFunction (@Gross_Amount [money])

RETURNS money

AS BEGIN RETURN @Gross_Amount/1.15 END

GO

DECLARE @gross [money] = 48.0000;

DECLARE @test1 [money] = (@Gross/1.15)*0.26*0.9; -- Sets 9.7670

DECLARE @Test2 [money] = dbo.TESTFunction(@Gross)*0.26*0.9; -- Sets 9.7669

SELECT @test1, @Test2;

Well, Vedran was right about the problem of the MONEY type. The function was rounding to 4 decimal places BEFORE the final calculation. When I changed the function's type to DECIMAL(25,13) (or even REAL), the differences with the original outputs disappeared.

BTW this is yet another example of a test that shows a problem that doesn't matter in the real application, where the result is only needed to the nearest penny anyway. But the regression testing was still worth doing - having fixed the small difference, I could then see and fix two genuine mistakes elsewhere in my code. And it was also a learning experience 🙂