資料庫黑洞

繼上次 ( 更精確的說法應該是一年前？ XD ) 分享的文章 [MSSQL] 欄位開立(1) - nvarchar, varchar, nchar, char的抉擇，跟大家簡單提到了文字欄位的抉擇，今天要再跟大家分享有關小數的欄位如何開，依照我個人的習慣，只選擇 decimal (因為目前還沒碰過需要有效數大於 38 的資料)。那為何不考慮 numeric, float, real, money 呢？文章會一一跟大家解釋 ☺

 

 

1.與小數有關的欄位型態比較

 

 

 

2.為何不選 numeric

這是一個很有深度的問題，在 MSDN 的 decimal and numeric 裡面有提到： numeric 和 decimal 型別兩者功能相等。

 

而在 Floating-point cheat sheet for SQL 一文則提到 SQL Decimal 的定義標準：

 

The standard defines two fixed-point decimal types:

• NUMERIC(M,N) has exactly M total digits, N of them after the decimal point
• DECIMAL(M,N) is the same as NUMERIC(M,N), except that it is allowed to have more than M total digits

 

換句話說，decimal 和 numeric 的差異在於，numeric(M, N) 型別所儲存的數字總數可以大於 M，而 decimal 必須依照實際定義的有效數 M 儲存 (不得超過)，在 SQL Server 的世界裡，則是將 decimal 和 numeric 皆實作為 「依照實際定義的有效數 M 儲存」，因此以規範來看，在 MSSQL 使用 decimal 是比較合乎規範的。

 

值得一提的是， MSSQL 雖然將 decimal 和 numeric 定義型別功能一樣，但是型別的轉換上，並不能視為是相等的。

 

舉例來說：

CREATE TABLE TableDecimal(a DECIMAL CONSTRAINT PK_TableDecimal PRIMARY KEY); 
GO 

--建立關聯表 (numeric 型別)
CREATE TABLE TableNumeric(a NUMERIC, CONSTRAINT FK_TableNumeric FOREIGN KEY (a) REFERENCES TableDecimal(a));

 

會出現如下錯誤

 

3.為何不選 float, real

參考 float and real ，float 和 real 僅為近似值，而非精準值，在資料呈現上不會全然正確。

 

 

使用時機：

(1) 當需要儲存的資料，有效數字超過 decimal 所能儲存的最大位數 (超過  38 位) 時。

(2) 避免用 float 或 real 欄位當作 WHERE 條件，特別是運算子 = 或 <> 。

 

4.為何不選 smallmoney, money

 

在 Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server? 中，有一個很棒的範例，說明 money 在計算上會有失準，範例如下

    @mon1 MONEY,
    @mon2 MONEY,
    @mon3 MONEY,
    @mon4 MONEY,
    @num1 DECIMAL(19,4),
    @num2 DECIMAL(19,4),
    @num3 DECIMAL(19,4),
    @num4 DECIMAL(19,4)

    SELECT
    @mon1 = 100, @mon2 = 339, @mon3 = 10000,
    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3
    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,
    @num4 AS numericresult

 

執行結果：

 

5.建議有關小數 / 金額的開法：

依照實際需求，盡可能的選擇儲存位元數較小的型別

decimal(9, n)：0 <= n <= 9，最大數 (9-n) 位數，儲存體位元組 5

decimal(19, n)：0 <= n <= 19，最大數 (19-n) 位數，儲存體位元組 9

decimal(28, n)：0 <= n <= 28，最大數 (28-n) 位數，儲存體位元組 13

decimal(38, n)：0 <= n <= 38，最大數 (38-n) 位數，儲存體位元組 17

 

例如：

(1) 沒有小數，且最大數目不會超過 (9 - 0) 位： 999,999,999 => decimal(9, 0)

(2) 2 位小數，且最大數目不會超過 (9 - 2) 位： 9,999,999 => decimal(9, 2)

(3) 有 2 位小數，且最大數目會超過 (9 - 2) 位，但不會超過(19-2)：99,999,999,999,999,999 => decimal(19, 2)

(4) 沒有小數，且最大數目會超過 (9 - 2) 位，但不會超過(19-2)：99,999,999,999,999,999 => decimal(19, 0)

 

參考資料

Difference between numeric,float and decimal in sql server

Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~