SQL SERVER 数值类型的存储格式及转换

SQL SERVER 数值类型的存储格式及转换

 

注：由于以前对NUMERIC/NUMERIC的理解错误，本文于2015.03.24重新编辑。

一．    整数
整数类型有四种：BigInt、Int、SmallInt和Tinyint，分别占用空间为8、4、2、1个字节。整数类型的存储格式比较简单：数据的补码形式。这个大家比较熟悉。

二．    Decimal/Numeric
这两个数据类型定义格式：decimal[(p[, s])] 和 numeric[(p[, s])]
p（精度）
指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是从 1 到最大精度之间的值。最大精度为38。
s（小数位数）
指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 p 之间的值。默认小数位数是 0，因而 0 <= s <= p。最大存储大小基于精度而变化。
精度与存储字节之间的关系：

那么SQL SERVER是如何存储Decimal/Numeric数据的呢？

首先右移小数点s位（不管小数点后面是否为0），使得数据成为整数（这是该类型数据成为精确类型的基础）。SQL SERVER对结果值专门使用一个字节记录正负：0x00表示负；0x01表示正。结果的绝对值（没有符号部分）存储于符号值其后。

举例：

create tableta(col1numeric(38, 2)) go insert intota values(812.5) go   实际存储的值：

以前，我还曾受CONVERT函数的误导： select col1, CONVERT(varbinary(100), col1, 1) from ta   以为保存的值中就包含精度和小数位，以及一位0，而且占用空间的大小是可变的，其实这都是错误的。

 

以上部分（符号位和结果值）构成了Decimal/Numeric类型数据的长度，也就是上表中占用的字节数。

 

ODBC： 最近在了解ODBC，发现其定义NUMERIC类型的结构如下： #define SQL_MAX_NUMERIC_LEN        16 typedef struct tagSQL_NUMERIC_STRUCT {   SQLCHAR  precision;   SQLSCHAR scale;   SQLCHAR  sign;    /* 1 表示正, 0 表示负 */   SQLCHAR  val[SQL_MAX_NUMERIC_LEN]; } SQL_NUMERIC_STRUCT;                         共19个字节，比SQL SERVER的NUMERIC多了精度和小数两个字节。

 三．    Money和 Smallmoney

货币类型有两种：Money和SmallMoney，分别占用8个和4个字节。货币类型固定有4位的小数位。
货币类型和Decimal/Numeric类型的存储格式有相似处也有不同处。
相似的地方是：SQL SERVER会将货币类型小数点右移4位（相当于乘以10000），这样就成整数了。
不同的地方是：SQL SERVER直接将小数点右移后的值按补码存储。

给货币类型赋值时，不需要用单引号 (') 引起来。请务必记住虽然您可以指定前面带有货币符号的货币值，但 SQL Server 不存储任何与符号关联的货币信息，它只存储数值。

四．    浮点数Float和real
Float类型定义格式：float [ ( n ) ]。 n 为用于存储科学记数法 float 数尾数的位数，同时指示其精度和存储大小。n 必须为从 1 到 53 之间的值。
   精度与存储字节之间的关系：

在 SQL Server 中，real 的同义词为 float(24)。
由于浮点数存储的数据的近似值，所以如果不是要保存特别大的数据，建议使用精确类型值。浮点数的存储格式请看博文。

五．    使用CONVERT将数值类型转换为字符型
1.    下表显示了从 float 或 real 转换为字符数据时的 style 值。

2.    在下表中，左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。

3.    若目的字符串太短，则会产生下面的错误：

六．    参考文献
1.    MSDN