認識 decimal 和 numeric：小數位數可能會被截斷

使用版本：
SQL Server 2005、2008、2008 R2
SSIS 2005、SSIS 2008

decimal 和 numeric 是具有固定有效位數和小數位數的數值資料類型。
例如，123.45 這個數字的有效位數是 5，小數位數是 2。

numeric 和 decimal 資料類型的預設最大有效位數為 38。在舊版的 SQL Server 中，預設的最大值是 28。

decimal 兩旁數值資料類型的有效位數和小數位數是固定的。
如果算術運算子有兩個相同類型的運算式，結果會有相同的資料類型，且會有定義給這個類型的有效位數和小數位數。

如果運算子有兩個含不同數值資料類型的運算式，資料類型優先順序的規則會定義這個結果的資料類型。

結果會有定義給它的資料類型的有效位數和小數位數。

下表定義在運算結果是 decimal 類型時，如何計算結果的有效位數和小數位數。

當符合下列中的任何條件時，結果便是 decimal：
(1) 兩個運算式都是 decimal。
(2) 一個運算式為 decimal，另一個運算式為優先順序低於 decimal 的資料類型。

--01

重要：

結果有效位數及小數位數的絕對最大值為 38。
當結果有效位數大於 38 時，會縮減對應的小數位數，以防止截斷結果的整數部分。

---

請參考以下的範例程式碼：

--01 建立範例資料表：SSIS_NUMERIC_Dest
USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SSIS_NUMERIC_Dest]') AND type in (N'U'))
DROP TABLE [dbo].[SSIS_NUMERIC_Dest]
GO
CREATE TABLE [dbo].SSIS_NUMERIC_Dest
(
	[pid] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[cNUMERIC] [numeric](18, 3) NULL,
	derived01 [numeric] (29,14),
	derived02 [numeric] (38,23),
	)
GO
INSERT SSIS_NUMERIC_Dest VALUES
	(11.111, 11.11111111111111, 11.11111111111111111111111),
	(22.222, 22.22222222222222, 22.22222222222222222222222),
	(33.333, 33.33333333333333, 33.33333333333333333333333),
	(44.444, 44.44444444444444, 44.44444444444444444444444),
	(55.555, 55.55555555555555, 55.55555555555555555555555),
	(66.666, 66.66666666666666, 66.66666666666666666666666),
	(77.777, 77.77777777777777, 77.77777777777777777777777),
	(88.888, 88.88888888888888, 88.88888888888888888888888),
	(99.999, 99.99999999999999, 99.99999999999999999999999);
GO

--02 原始資料
SELECT pid, cNUMERIC N'numeric(18,3)', derived01 N'numeric(29,14)', derived02 N'numeric(38,23)'
FROM dbo.SSIS_NUMERIC_Dest
GO

--02

--03 尚未轉型的加減乘除運算
SELECT pid, cNUMERIC N'numeric(18,3)', derived01 N'numeric(29,14)',
	(cNUMERIC + derived01) N'(18,3)+(29,14)', (cNUMERIC - derived01) N'(18,3)-(29,14)', 
	(cNUMERIC * derived01) N'(18,3)*(29,14)', (cNUMERIC / derived01) N'(18,3)/(29,14)'
FROM dbo.SSIS_NUMERIC_Dest
GO

--03

觀察到：
在執行乘、除法運算時，小數位被截斷了。

以乘法與除法來看：
因為結果有效位數大於 38 時，會縮減對應的小數位數，以防止截斷結果的整數部分。

以加法來看：
結果小數位數的長度公式是：max(s1, s2)
計算的結果是：22.22211111111111，小數位數有：14位。
所以，取到小數位數：14位。

以減法來看：
結果小數位數的長度公式是：max(s1, s2)
計算的結果是：-0.00011111111111，小數位數有：14位。
所以，取到小數位數：14位。

--04 若是改以「運算式(expression)」 方式，讓兩個數值直做運算
SELECT pid, cNUMERIC N'numeric(18,3)', derived01 N'numeric(29,14)',
	(cNUMERIC * derived01) N'(18,3)*(29,14)', (cNUMERIC / derived01) N'(18,3)/(29,14)',
	(11.111 * 11.11111111111111) N'* 乘法直接運算', (11.111 / 11.11111111111111) N'/ 除法直接運算'
FROM dbo.SSIS_NUMERIC_Dest
WHERE pid = 1
GO

--04

可以觀察到：若是改以「運算式(expression)」 方式，讓兩個數值直做運算，其小數位數可以更加的精確。
乘法運算：小數位數是到：17位。
除法運算：小數位數是到：20位。



接下來，觀察 cNUMERIC 資料行後，將其轉型為 numeric(5,3)。

再度執行加減乘除的運算：

--05 事先轉型為合適的有效位數、小數位數後，再執行運算
SELECT pid, cNUMERIC N'numeric(18,3)', derived01 N'numeric(29,14)',
	(CAST(cNUMERIC AS numeric(5,3)) + derived01) N'+', (CAST(cNUMERIC AS numeric(5,3)) - derived01) N'-', 
	(CAST(cNUMERIC AS numeric(5,3)) * derived01) N'*', (CAST(cNUMERIC AS numeric(5,3)) / derived01) N'/'
FROM dbo.SSIS_NUMERIC_Dest
GO

--05

觀察其小數位數：
加法與減法：：小數位數是到：14位。
結果小數位數之公式：max(s1, s2)
也就是 max(3,14) 取 14。

乘法運算：小數位數是到：17位。
結果小數位數之公式：s1 + s2。
也就是 3+14 = 17。

除法運算：小數位數是到：22位。
但結果有效位數及小數位數的絕對最大值為 38。
當結果有效位數大於 38 時，會縮減對應的小數位數，以防止截斷結果的整數部分。

---

建議作法：

參與運算的資料行： cNUMERIC、derived01，請事先轉型為合適的有效位數、小數位數。

在 SSIS 中，執行相關運算也是會遇到此問題，例如：使用「衍生的資料行(Derived Column)」。

---

decimal[ (p[ ,s] )] 和 numeric[ (p[ ,s] )]

固定有效位數和小數位數的數字。
當使用最大有效位數時，有效的值是從 - 10^38 +1 到 10^38 - 1。

decimal 的 ISO 同義字是 dec 和 dec(p、s)。numeric 與 decimal 的功能相同。

p (有效位數)
可儲存的最大十進位數總數，小數點左右兩側都包括在內。
有效位數必須是 1 至最大有效位數 38 之間的值。
預設有效位數是 18。

s (小數位數)
小數點右側所能儲存的最大十進位數。
小數位數必須是從 0 到 p 的值。
只有在指定了有效位數時，才能指定小數位數。
預設小數位數是 0；因此，0 <= s <= p。最大儲存體大小會隨著有效位數而不同。

---

參考資料：

有效位數、小數位數和長度 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms190476.aspx

decimal 和 numeric (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187746.aspx

SSIS：調整「衍生的資料行」轉換的資料類型屬性
http://sharedderrick.blogspot.com/search/label/SSIS%202008#uds-search-results