Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To get the real precision and scale for the result (@val1/@val2) I would execute this <code>T-SQL</code> script </p> <pre><code>DECLARE @val1 NUMERIC(38,16); DECLARE @val2 NUMERIC(38,16); SET @val1 = 0.01; SET @val2 = 0.0074464347; SELECT @val1/@val2 ,SQL_VARIANT_PROPERTY(@val1/@val2, 'BaseType') [BaseType] ,SQL_VARIANT_PROPERTY(@val1/@val2, 'Precision') [Precision] ,SQL_VARIANT_PROPERTY(@val1/@val2, 'Scale') [Scale] </code></pre> <p>And the results will be:</p> <pre><code>(No column name) BaseType Precision Scale 1.342924 numeric 38 6 </code></pre> <p>So, the result precision is 38 and result scale is 6. MSDN has some details regarding precision and scale for arithmetic operations. These information can be found here: <a href="http://msdn.microsoft.com/en-us/library/ms190476.aspx" rel="nofollow">Precision, Scale, and Length (Transact-SQL)</a>:</p> <pre><code>Operation = e1 / e2 Result precision = p1 - s1 + s2 + max(6, s1 + p2 + 1) Result scale * = max(6, s1 + p2 + 1) </code></pre> <p>Using these formulas we can write the next <code>T-SQL</code> script to get theoretical precision and scale for the result (@val1/@val2) :</p> <pre><code>SELECT @p1 = 38 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val1, 'Precision')) ,@s1 = 16 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val1, 'Scale')) ,@p2 = 38 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val2, 'Precision')) ,@s2 = 16 --or CONVERT(INT, SQL_VARIANT_PROPERTY(@val2, 'Scale')); --SELECT @p1 [@p1], @s1 [@s1], @p2 [@p2], @s2 [@s2]; SELECT @p_result = @p1 - @s1 + @s2 + CASE WHEN 6 &gt;= @s1 + @p2 + 1 THEN 6 WHEN 6 &lt; @s1 + @p2 + 1 THEN @s1 + @p2 + 1 END ,@s_result = CASE WHEN 6 &gt;= @s1 + @p2 + 1 THEN 6 WHEN 6 &lt; @s1 + @p2 + 1 THEN @s1 + @p2 + 1 END; SELECT @p_result [@p_result], @s_result [@s_result]; </code></pre> <p>The result is:</p> <pre><code>@p_result @s_result 93 55 </code></pre> <p>So, for this arithmetic operation (@val1/@val2), in <code>theory</code> the precision and scale are <code>93</code> and <code>55</code> but the real precision and scale are <code>38</code> and <code>6</code>. The real precision is 38 because <em>"<strong>The result precision</strong> and scale <strong>have an absolute maximum of 38</strong>"</em>. </p> <p>Regarding the real result scale (6) MSDN it's not clear: <em>"When a result precision is greater than 38, <strong>the corresponding scale is reduced</strong> to prevent the integral part of a result from being truncated"</em>.</p> <p>To see how "the corresponding scale is reduced" I executed the above tests (script 1 for real precision and scale and script 2 for theoretical precision and scale) using <code>NUMERIC</code> values having the same scale (16) but different scales (from 16 to 38). The results of these tests are:</p> <pre><code>/* Result prec. Result scale (T=theoretical value, R=real value) T-R T-R --@val1 and @val2 data type 49-38 33-22 --NUMERIC(16, 16) 51-38 34-21 --NUMERIC(17, 16) 53-38 35-20 --NUMERIC(18, 16) 55-38 36-19 --NUMERIC(19, 16) ... 61-38 39-16 --NUMERIC(22, 16) -- &lt;-- data type for [real] result scale 16 ... 77-38 47-8 --NUMERIC(30, 16) 79-38 48-7 --NUMERIC(31, 16) 81-38 49-6 --NUMERIC(32, 16) 83-38 50-6 --NUMERIC(33, 16) 85-38 51-6 --NUMERIC(34, 16) ... 93-38 55-6 --NUMERIC(38, 16) */ </code></pre> <p>Examining these results:</p> <p>1.I see an arithmetic progression for the real result scale: from 22 to 6, step -1.</p> <p>2.Also, if the scale for @val1 and @val2 is constant (<code>NUMERIC(...,16)</code>) then an inverse correlation exists between @val1 &amp; @val2 precision (from 16 to 32) and the [real] result scale (from 16 to 6).</p> <p>3.If @val1 and @val2 precision is 32 or higher (<code>NUMERIC(32-&gt;38,16)</code>) the the [real] result scale is always 6 => this is your case.</p> <p>4.If a greater [real] result scale is needed (over 6) you need to use a lower precision for @val1 and @val2: <code>NUMERIC(22, 16)</code>:</p> <pre><code>SELECT CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) [CONVERT(NUMERIC(22,16)] ,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'BaseType') [BaseType] ,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'Precision') [Precision] ,SQL_VARIANT_PROPERTY( CONVERT(NUMERIC(22,16),@val1) / CONVERT(NUMERIC(22,16),@val2) , 'Scale') [Scale] CONVERT(NUMERIC(22,16) BaseType Precision Scale ---------------------- -------- --------- ----- 1.3429245542165299 numeric 38 16 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL Numeric data type truncating value?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USBogdan Sahlean
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQL Numeric data type truncating value?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COExcellent. You're a genius and this is very much appreciated!
      singulars
      1. PO
      1. USTom Miller
    2. COI'm just a SQL Server enthusiast. Nothing more.
      singulars
      1. PO
      1. USBogdan Sahlean
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload