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

c#, sql-server, sql-server-2012, sqldatatypes

Solution

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:

- 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

- 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

Problem

Original source