I'm trying to store metric data (meters, kilometers, square-meters) in SQL Server 2012.
我正在尝试在SQL Server 2012中存储度量数据(米,公里,平方米)。
What is the best datatype to use? float (C#: double), decimal (C#: decimal) or even geometry? Or something different?
什么是最好的数据类型? float(C#:double),decimal(C#:decimal)甚至几何?或者不同的东西?
3 个解决方案
#1
3
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.
如果我们谈论的是架构,那么精度需求相对有限,C#32位浮点数将带您走很长的路。在SQL中,这转换为float(24),也称为数据库类型real。此SQL DB类型每个条目需要4个字节的存储空间。
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.
如果我们想要解决地球表面上的点,则需要更高的精度。这里的C#double是合适的,它对应于SQL float(53)或者只是float。此SQL DB类型需要8个字节的存储空间,并且只应在需要时使用,或者如果应用程序很小并且不考虑磁盘/内存使用情况。
The SQL Decimal is could be a good alternative for the actual SQL DB, but has 2 drawbacks:
SQL Decimal可能是实际SQL DB的一个很好的替代方案,但有两个缺点:
- It corresponds to a
C# Decimalwhich 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
“十进制值类型适用于需要大量有效积分和小数位且没有舍入误差的财务计算。” - MSDN:十进制结构
- The SQL DB type
Decimalrequires 5-9 bytes of storage per entry (depending on the precision used), which is larger than the float(x) alternatives.
SQL DB类型Decimal每个条目需要5-9个字节的存储空间(取决于使用的精度),这大于float(x)替代方案。
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
最后,这是一个有用的资源,用于在.Net和SQL之间转换不同类型:SqlDbType Enumeration
#2
4
Either a decimal with an appropriate amount of precision for your data, or an int type, if appropriate
要么是具有适当数据精度的小数,要么是int类型(如果适用)
#3
1
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或double是非精确数据类型(因为舍入问题5.0 == 5.0可能是假的)十进制是一个确切的数据类型(因此5.0 == 5.0将永远为真)和几何/地理(容易)说)是在地图上的位置。
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.
浮点计算应该是三者之间的关系,因为地理是二进制数据,有一些关于投影的信息(这里所有关于地图)和十进制在技术上不像浮点数那样容易处理。
#1
3
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.
如果我们谈论的是架构,那么精度需求相对有限,C#32位浮点数将带您走很长的路。在SQL中,这转换为float(24),也称为数据库类型real。此SQL DB类型每个条目需要4个字节的存储空间。
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.
如果我们想要解决地球表面上的点,则需要更高的精度。这里的C#double是合适的,它对应于SQL float(53)或者只是float。此SQL DB类型需要8个字节的存储空间,并且只应在需要时使用,或者如果应用程序很小并且不考虑磁盘/内存使用情况。
The SQL Decimal is could be a good alternative for the actual SQL DB, but has 2 drawbacks:
SQL Decimal可能是实际SQL DB的一个很好的替代方案,但有两个缺点:
- It corresponds to a
C# Decimalwhich 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
“十进制值类型适用于需要大量有效积分和小数位且没有舍入误差的财务计算。” - MSDN:十进制结构
- The SQL DB type
Decimalrequires 5-9 bytes of storage per entry (depending on the precision used), which is larger than the float(x) alternatives.
SQL DB类型Decimal每个条目需要5-9个字节的存储空间(取决于使用的精度),这大于float(x)替代方案。
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
最后,这是一个有用的资源,用于在.Net和SQL之间转换不同类型:SqlDbType Enumeration
#2
4
Either a decimal with an appropriate amount of precision for your data, or an int type, if appropriate
要么是具有适当数据精度的小数,要么是int类型(如果适用)
#3
1
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或double是非精确数据类型(因为舍入问题5.0 == 5.0可能是假的)十进制是一个确切的数据类型(因此5.0 == 5.0将永远为真)和几何/地理(容易)说)是在地图上的位置。
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.
浮点计算应该是三者之间的关系,因为地理是二进制数据,有一些关于投影的信息(这里所有关于地图)和十进制在技术上不像浮点数那样容易处理。