Hello Claude. Congrats on publishing your first article on SQL Server Central :-). I think this article is off to a good start, but is incomplete and could use a bit more explanation and examples. For example:
- What does “«preemptive» truncation” mean?
- What is the output of the initial T-SQL statements? (please see example code below)
- What about the rules for precision / scale when it comes to DECIMAL operations? Please see the documentation for “Precision, scale, and Length” (about 1/3 the way down the page).
Why use DECIMAL(38, 18) in the first place?
- What do you mean by “culture-independent”? Some of us know, but I suspect many readers won’t.
- How does one make use of the .NET code shown here? Is it purely a utility for helping with SQLCLR projects? If this is supposed to return a “compacted” T-SQL DECIMAL type, how would you return that from SQLCLR given that the return type (including precision and scale) need to be declared when creating the T-SQL wrapper function? Maybe provide some examples of it being used..
For the initial T-SQL example code in the article that is trying to explain the problem to the reader, you really should show the output of those statements since many readers will not have SSMS open and hence not be able to execute those statements to see what they produce. Below is an example of how to show the output. I also included an additional variable to show the effect of using a properly sized DECIMAL type:
DECLARE @D1 DECIMAL(38, 18) = 1.123456789012345678;
DECLARE @D2 DECIMAL(38, 18) = 1.0;
DECLARE @D3 DECIMAL(1, 0) = 1.0;
DECLARE @D4 DECIMAL(20, 18) = 1.123456789012345678;
SELECT @D1 AS [D1 — DECIMAL(38, 18)], — 1.123456789012345678
@D4 AS [D4 — DECIMAL(20, 18)]; — 1.123456789012345678
SELECT @D1 * @D2 AS [DEC(38, 18) * DEC(38, 18)], — 1.123457
@D1 * @D3 AS [DEC(38, 18) * DEC(1, 0)], — 1.1234567890123457
@D4 * @D2 AS [DEC(20, 18) * DEC(38, 18)]; — 1.123456789012346
Good luck! Take care, Solomon…