Just for fun...

Using a lookup table improves performance, a bit suprising (for me at least).

// Pre-calculated sums of 0 to 99

static readonly int[] TwoDigitSums = new int[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,

1, 2, 3, 4, 5, 6, 7, 8, 9, 10,

2, 3, 4, 5, 6, 7, 8, 9, 10, 11,

3, 4, 5, 6, 7, 8, 9, 10, 11, 12,

4, 5, 6, 7, 8, 9, 10, 11, 12, 13,

5, 6, 7, 8, 9, 10, 11, 12, 13, 14,

6, 7, 8, 9, 10, 11, 12, 13, 14, 15,

7, 8, 9, 10, 11, 12, 13, 14, 15, 16,

8, 9, 10, 11, 12, 13, 14, 15, 16, 17,

9, 10, 11, 12, 13, 14, 15, 16, 17, 18 };

// Handle NULLs by adding RETURNS NULL ON NULL INPUT to wrapper if required

[Microsoft.SqlServer.Server.SqlFunction]

public static int SumDigits(int Input)

{

return (Input >= 0 ? TwoDigitSums[Input % 100] +

TwoDigitSums[(Input / 100) % 100] +

TwoDigitSums[(Input / 10000) % 100] +

TwoDigitSums[(Input / 1000000) % 100] +

TwoDigitSums[(Input / 100000000) % 100]

: TwoDigitSums[-(Input % 100)] +

TwoDigitSums[(Input / -100) % 100] +

TwoDigitSums[(Input / -10000) % 100] +

TwoDigitSums[(Input / -1000000) % 100] +

TwoDigitSums[(Input / -100000000) % 100]);

}

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537