搜尋本站文章

2011-08-11

認識 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