How do I store trailing zeros with decimals? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How do I store trailing zeros with decimals?

Author

Topic

rupertsland
Starting Member

9 Posts

Posted - 2011-08-24 : 11:36:06

Hello.

I am helping our company implement an environmental (i.e., biological) database to store fish, bug, and water chemistry data.

My question is what data type should I use to store a trailing zero to preserve significant digits?

For example, we get back water chemistry data from various labs where each analysis parameter (e.g., mecurcy, iron, calcium, etc.) has a different number of digits to the right of the decimal (has to do with the methodology). The results are stored in a single column.

Sample  Parameter  Result   Detection Limit
1       Mercury    0.00010  0.00020
1       Iron       0.10     0.05
1       Calcium    15.10    5.00
2       Mercury    0.00002  0.00020
3       Mercury    0.00005  0.00010  <- new limit (different machine used)

I tried using decimal, numeric, and float data types. I can't use decimal/numeric because the number of decimal places are fixed by the precision and scale.

Float simply truncates / chops off any trailing zero. This seems to be the case with any database software (e.g., Double datatype in Access).

My only recourse is to store the data as text. For us in the biology/chemistry field, this becomes problematic. We want the results stored as numbers so that we can use the data for calculations. And we have to work with hundreds of thousands of rows of data. We typically calculate means, min, max, and mode on all water chemistry data, and then use the data for GIS models.

Is there any other way in SQL Server I could preserve the trailing zeros?

Rupertsland,
Winnipeg Canada

-------------------
Dirk Schmid, M.Sc.
Environmental Microbiologist
Winnipeg, Canada

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-24 : 11:46:12

You could declare the column as sql_variant and then store each value as the precise decimal type:

create table #test(limit sql_variant)
insert #test values(cast(0.00020 as decimal(6,5)))
insert #test values(cast(0.05 as decimal(3,2)))
insert #test values(cast(5.00 as decimal(3,2)))
insert #test values(cast(0.00020 as decimal(6,5)))
insert #test values(cast(0.00010 as decimal(6,5)))
insert #test values(0.00010)

select *, SQL_VARIANT_PROPERTY(limit,'BaseType') Type, 
SQL_VARIANT_PROPERTY(limit,'Precision') Precision, SQL_VARIANT_PROPERTY(limit,'Scale') Scale
from #test

You'd have to use an explicit CAST or CONVERT to ensure the digits are preserved (see SQL_VARIANT_PROPERTY on last value for example)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-24 : 15:35:14

How about using any numeric data type with max precision you have in data and storing the precision as an additional column? Then you could solve a presentation issue on the client.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

rupertsland
Starting Member

9 Posts

Posted - 2011-08-30 : 22:11:49

I want to thank you robvolk and mmarovic for your help.

I will consider your solutions and do some further testing.

Dirk

-------------------
Dirk Schmid, M.Sc.
Environmental Microbiologist
Winnipeg, Canada

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources