I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are


My clients get lab measurements like these and the number of decimal points tell what typee of instrumentation, methods etc. the lab used. They require to store the data as it is (not 1.21 for 1.210). For now I have a custom data type which implements varchar type as a base type, but the developers do not like it as the conversion to use mathematical operators involve some overhead. Is there any numeric type solution to handle this type of issue?

  • 9
    Why not just store it all as numeric(x,5) then have another column that indicates the original scale? Jan 31, 2014 at 18:50
  • 1
    It was definitely a good solution and I thought about it too. Just exploring options to achieve the goal without data redundancy and without much alteration to existing data loading routines. Jan 31, 2014 at 21:16

2 Answers 2


I would store this as two different INT/BIGINT, one for each side of the decimal point. To make it easier to query, you can also add a computed column that renders the data as a varchar. In other words:

  IntegerPart INT NOT NULL 
  , FractionPart INT NULL /* Null of no fraction */
  , HumanFormat AS CAST(IntegerPart AS VARCHAR) 
      + '.' + COALESCE('.'+CAST(FractionPart AS VARCHAR), '')

Optionally, add more computed columns to store as float or other formats that you might need to calculate.


I believe the better answer to be Martin's reply. I would use 2 columns: a numeric(x,y) and a smallint to preserve the original scale.

It has the following advantages compared to the int.int or varchar solution:

  • can be sorted properly
  • can leverage sql functions/where clauses min/max/sum/ >=< etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.