詳細分析sqlserver中的小數型別（float和decimal）

在SQL Server中實際上只有兩種小數數值型別，分別是float（近似數值）和decimal（精確數值），這兩種型別能表示所有的小數數值型別。

float（近似數值型別）

float表示的是近似數值，存在一定的精度缺失。

float(n)

這裡的n是以科學計數法儲存浮點數尾數的位數，因此此引數決定了精度和儲存的大小。其是可選的，預設值是53，即float等價於float(53)，佔用8bytes。如果指定了n，則它必須是介於1至53之間的值。實際上，雖然n的取值範圍定義是1至53，但實際上float只能表示float(53)和float(24)兩種型別，分別佔用8bytes和4bytes。

使用近似數值要格外注意儘量避免相等比較，因為比如1可以被儲存為1.000000056，也可以被儲存為1.00000000672，進行相等比較會得到意料之外的結果。

decimal（精確數值型別）

decimal表示的是精確數值型別。不存在精度損失，別名是numeric。

decimal(p, s)
 -- 等價於
 numeric(p, s)

精確數值型別需要分別指定小數的最大位數（p）和小數位的數量（s）：

• p（precision）：指定小數的最大位數，小數點的左側和右側的數字的總數量不能超過p，p的取值範圍是從1到38，預設值為18。

• s（scale）：指定在小數點右側的小數位數，p-s是小數點左邊的最大位數。s必須是從0到p的值，只有在指定了精度的情況下才能指定s，s的預設值是0，因此，0 <= s <= p。
  

p的大小也同時決定了儲存位數的大小：

因為p和s必須遵守規則：0 <= s <= p <= 38，所以decimal(p, s)實際上能夠表示的有效值是從-10^38+1到10^38-1。這就意味著，decimal資料型別的最大精度是38，即最多可以儲存38位數字，所有這些數字均可位於小數點後面。decimal資料型別儲存精確的數字表示形式，沒有近似值。

小數的除法

小數的除法實際上是近似運算，因此在使用除法的時候SQL Server會自動將小數的型別提升為float型別（隱式資料型別升級）。

小數常量的預設資料型別是decimal，但是float型別的優先順序比decimal型別要高。在預設的情況下，SQL Server會將小數數值的常量自動轉換為decimal資料型別（常態下），而在進行小數的除法運算的時候，則會就近進行資料型別的升級，轉換為float(24)或float(53)資料型別（運算時）。

簡單舉個例子，常量12.345在常態下會被解析並轉換為numeric(5, 3)的數值型別，即使用最小精度5和最小小數位數3；而在運算除法時，比如12.345/2則會被解析並轉換為float(24)，即最小精度的近似數值型別。

小數轉為字串

相比cast(float_expression as float(24/53))，使用str()函式更能夠有效控制近似數值的小數位數，因為str()函式獲取的是近似數值。

  str(float_expression [ , length [ , decimal ] ])

str()函式可以接受length、decimal兩個引數，皆是可選的。

• length是小數的總位數，包含正負符號，小數點，小數點左邊和右邊數字個數之和；

• decimal是小數位的數量（小數點右邊數字個數），小數位最大為16位，不能超過16，否則，會被截斷為16位。如果小數位沒有decimal多，那麼右邊補0。

• 返回值是varchar型別。
  

將decimal常量轉換為varchar型別：

  select str(123.45, 10, 2); -- 123.45

將float表示式的值轉換為varchar型別（位數不足自動補0）：

  select str(1.0/3, 10, 8); -- 0.33333300

對小數常量轉換為varchar型別，減少小數位的數量，由2位減少為1位（會自動進行四捨五入運算）：

  select str(123.45, 6, 1); -- 123.5

使用函式str或cast將float和decimal強制轉換為varchar型別時，返回的數值可能是不相同的：

  select str(56.64564684439527, 38, 20); -- 56.64564684438742000000  select cast(56.64564684439527 as varchar(100)); -- 56.64564684439527

這是因為兩種函式的處理方式的不同導致的：str()函式會對小數數值先取近似值；而cast()函式則是返回與原始值資料型別相同的值（decimal返回精確值，float返回近似值）。