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?
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:
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
Decimal
requires 5-9 bytes of storage per entry (depending on the precision used), which is larger than the float(x) alternatives.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