Numeric data type with variable significant digits

Question

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?

Answer 1

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.

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.