What datatype to use when storing metric data in SQL databases?

Question

I'm trying to store metric data (meters, kilometers, square-meters) in SQL Server 2012.

What is the best datatype to use? float (C#: double), decimal (C#: decimal) or even geometry? Or something different?

Answer 1

Either a decimal with an appropriate amount of precision for your data, or an int type, if appropriate

Answer 2

It completely depends on the application and what precision you need for it.

If we are talking about architecture then then precision needs are relatively limited and a C# 32-bit float will take you a long way. In SQL this translates to float(24), also referred to as the database type real. This SQL DB type requires 4 bytes of storage per entry.

If we instead want to address points on the surface of the earth you need a lot higher precision. Here a C# double is suitable, which corresponds to a SQL float(53) or just float. This SQL DB type requires 8 bytes of storage and should be used only if needed or if the application is small and disk/memory usage is not a concern.

The SQL Decimal is could be a good alternative for the actual SQL DB, but has 2 drawbacks:

1. It corresponds to a C# Decimal which is a type designed for financial usage and to prevent round-off errors. This design renders the C# Decimal type slower than a float/double when used in trigonometric methods etc. You could of course cast this back and forth in your code, but that is not the most straight-forward approach IMO.
   

   "The Decimal value type is appropriate for financial calculations requiring large numbers of significant integral and fractional digits and no round-off errors." - MSDN : Decimal Structure

2. The SQL DB type Decimal requires 5-9 bytes of storage per entry (depending on the precision used), which is larger than the float(x) alternatives.

So, use it according to your needs. In your comment you state that its about real estate, so I'd go for float(24) (aka real) which is exactly 4 bytes and directly translates to a C# float. See: float and real (Transact-SQL)

Lastly, here is a helpful resource for converting different types between .Net and SQL: SqlDbType Enumeration

Answer 3

Depends what you want to do
float or double are non-exact datatypes (so 5.0 == 5.0 may be false due to rounding issues)
Decimal is an exact datatype (so 5.0 == 5.0 will always be true)
and Geometry/Geography (easy said) are for locations on a map.

Float calculation should be fastes among the three, since geography is binary data with some infomation about projection (ist all about maps here) and decimal technically not as easy to handle as float.