locked
More than 7 decimal places in a number. RRS feed

  • Question

  • SQL Server MDS seems to limit you to 7 decimal places in numeric values. Does anyone know if this is configurable somewhere? We need at least nine decimals in some of our numbers.
    • Edited by wsmckenz Thursday, October 25, 2012 1:59 PM
    Thursday, October 25, 2012 1:50 PM

Answers

  • It appears you can create numeric attributes with more than 7 decimal places if you use the Stored Procs (via TSQL or Managed Code) as opposed to the Web UI or MDS Add-On. For example (using MDS w/ SQL Server 2012), I was able to use the [mdm].[udpAttributeChange] stored proc to change an existing attribute from 2 decimal places to 9...


    In the Administration section of the Web UI, it still shows the attribute data type as being 7 decimal places...



    • Edited by BillAnton Tuesday, October 30, 2012 1:36 AM specify SQL 2012
    • Marked as answer by Elvis Long Monday, November 5, 2012 1:06 AM
    Tuesday, October 30, 2012 1:34 AM
  • But in the Explorer section of the Web UI, I'm able to change the values to 9 decimal places w/out any errors.

    Same goes for the excel add-in...

    • Marked as answer by Elvis Long Monday, November 5, 2012 1:06 AM
    Tuesday, October 30, 2012 1:34 AM

All replies

  • It appears you can create numeric attributes with more than 7 decimal places if you use the Stored Procs (via TSQL or Managed Code) as opposed to the Web UI or MDS Add-On. For example (using MDS w/ SQL Server 2012), I was able to use the [mdm].[udpAttributeChange] stored proc to change an existing attribute from 2 decimal places to 9...


    In the Administration section of the Web UI, it still shows the attribute data type as being 7 decimal places...



    • Edited by BillAnton Tuesday, October 30, 2012 1:36 AM specify SQL 2012
    • Marked as answer by Elvis Long Monday, November 5, 2012 1:06 AM
    Tuesday, October 30, 2012 1:34 AM
  • But in the Explorer section of the Web UI, I'm able to change the values to 9 decimal places w/out any errors.

    Same goes for the excel add-in...

    • Marked as answer by Elvis Long Monday, November 5, 2012 1:06 AM
    Tuesday, October 30, 2012 1:34 AM
  • There's also a bit of a design-flaw in the way decimal attributes are added in that sproc. 

    mdm.udpAttributeSave  (which is just called by mdm.udpAttributeSaveByMUID, which the Web UI uses) contains a couple bits like the following..

    So the original bug is that the UI won't let you create anything beyond 7 decimal places.  If you don't specify a scale, it just creates a Decimal(38,0).  But if you look at the subtraction used below, it reduces the precision by the scale amount.  So you want 8 decimal places, it'd create a Decimal(30,8).  I know not all that many folks use a scale beyond 19 decimal places, but if you did w/ this code, it'd break.  I also realize it's uncommon to have really large numbers that also require a large scale, but this seems strange.

    Seems that whoever created this didn't really understand how SQL decimal/numeric types.  Of course this doesn't speak to the inneficiency of storing integers by default as a Decimal(38,0)... which takes up 17 bytes of storage vs. 4 for a standard INT.   Guess I need to post this on the Connect site so it can get fixed.

    /Facepalm

    END ELSE IF @DataType_ID = @DataTypeNumber BEGIN     
                            IF @DataTypeInformation < 0 SET @DataTypeInformation = 0; --DECIMAL(38, 0) is minimum precision allowed by SQL    
                            ELSE IF @DataTypeInformation > 38 SET @DataTypeInformation = 38; --DECIMAL(38, 38) is maximum precision allowed by SQL    
                            SET @SQL = @SQL + N'DECIMAL(' + CONVERT(NVARCHAR(2), 38 - @DataTypeInformation) + N', ' + CONVERT(NVARCHAR(2), @DataTypeInformation) + N') NULL;';    

    Wednesday, February 27, 2013 5:15 PM
  • That which we see on the UI is the MDS view. The MDS View cannot be altered. The scale (number of decimals) of the attribute can be altered in the MDS physical table. This would appear after the subscription refreshing.
    Wednesday, December 6, 2017 10:01 AM