4

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

1.002
1.210
0.00100
0.1401

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? – Martin Smith Jan 31 '14 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. – user353gre3 Jan 31 '14 at 21:16
3

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:

CREATE TABLE MyData
(
  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.

|improve this answer|||||
2

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.
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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