MSSQL數學運算應注意事項

每種資料庫均有其特性及限制，SQL Server在數值運算上，有一些特性與一般概念上有些許之差異，因此在Schema設計或撰寫SQL時，需特別注意否則將出現「溢位（Overflow）」、「捨位」誤差等。以下針對可能發生的問題進行說明。

捨位問題

在MSDN官方文件在除法運算中提及「如果整數 dividend 除以整數 divisor，結果就是整數[1]，結果的任何小數點後數字部分都會截斷。」，先前探討餘數、商數時也應用此項特性，直接將7除以3所得的結果即為商數2；而若需得到其真實運算結果2.33333(7/3)則該如何達成，在此將重提SQL SERVER數學運算上重要的概念，即官方網站中資料類型優先順序所提及「當一個運算子結合兩個不同資料類型的運算式時，資料類型優先順序的規則，會指定將低優先順序的資料類型，轉換為高優先順序的資料類型。[2]」，資料類型優先順序請參考前述之官方網站說明。以下將自7除以3為範例，將列舉出下述四種不同作法及其結果。 

測試	運算	結果	資料類型
1	7/ 3	2	int（整數）
2	7.0 /3 或 7 / 3.0	2.333333	numeric
3	CAST(7 AS numeric) / 3	2.333333	numeric
4	CAST(1 AS float) * 7 / 3	2.33333333333333	float

註:請特別注意numeric及float之差異，當轉換為float時，可能因成Analog Digital之轉換而發生捨位誤差。

l   測試1

結果為商數（Quotient），7及3均為整數（int），結果也將為整數。

l   測試2

將運算元（Operand）即7及3直接補上.0，即代表將int轉換成numeric。

l   測試3

利用CAST型態轉換函數，將運算元型態進行轉換。

l   測試4

額外乘上浮點數1的方法，此方法主要想法是不去更動原運算元，由於此方法額外乘上浮點數1，因此運算式中運算元將增加為3，在此探討額外應注意的事項為「順序」。以下調整測試4中的運算元順序如下，並列出運算結果。

運算	結果
CAST(1 AS float) * 7 / 3	2.33333333333333
7 / 3 * CAST(1 AS float)	2

範例中所使用的乘法（*）或除法（/）運算子（Operator）兩者具有相同的運算優先順序，當運算式中的兩個運算子有相同的運算子優先順序等級時，會依據它們在運算式中的位置，由左至右來運算及評估。7 / 3 * CAST(1 AS float)運算中7/3將優先被處理，運算結果為2（整數），即取得商數的作法，再將此結果與後續運算子進行運算，即2 * CAST(1 AS float)運算結果仍為2。

前述範例中，探討一般概念對於除法運算結果可為浮點數的刻版印象而造成的「捨位」誤差，再次強調SQL SERVER中整數除法運算後結果仍為整數的規則。在此將探討另外一項重要問題「溢位（Overflow）」。

溢位（Overflow）問題

以下探討乘法運算時，運算結果超過原資料型態的上限而發生溢位錯誤，將以1百萬（Million、10^6）為範例資料進行10⁶ * 10⁶ / 10⁶ = 10⁶運算測試，如下列兩組SQL語法所示： 

#	SQL	結果
1	SELECT Million*Million / Million FROM   (   SELECT 1000000 Million   ) A	Arithmetic overflow error converting expression to data type int.
2	SELECT CAST(Million AS bigint)* Million / Million FROM  ( SELECT 1000000 Million ) A	1000000

測試資料1000000，就其數值範圍其型態應歸為整數（int），int的數值範圍為-2^31 (-2,147,483,648)到2^31-1(2,147,483,647)。

l   測試1

SQL中Million*Million結果將為10^12（值相當於2^39.86，可用LOG對數函數計算即得到），因資料值已超過int資料型態上限，將造成溢位錯誤。

l   測試2

SQL是將資料轉換成bigint，用以承受此運算值範圍，此範例僅在於強調運算時將可能發生溢位錯誤，實務上常發生於SUM()加總函數上，當針對資料型態為int的欄位進行彙總運算，由於SUM()函數傳入引數的欄位為int，回傳結果型態也為int，將以兩筆2^30(1073741824)資料加總為例，因加總值超過int允許範圍而發生溢位錯誤。

SQL	結果
SELECT SUM(Val) FROM ( SELECT POWER(2, 30) Val --1073741824 UNION ALL SELECT POWER(2, 30) ) A	Arithmetic overflow error converting expression to data type int.

以下將SQL SERVER 2005及2008於官方文件上所定義的SUM函數的「傳回類型」合併表列如下。

SQL SERVER 2005		SQL SERVER 2008
運算式結果 傳回類型 整數類別目錄 int decimal 類別目錄 (p, s) decimal(38, s) money 和 smallmoney 類別目錄 money float 和 real 類別目錄 float		運算式結果 傳回類型 tinyint int smallint int int int bigint bigint decimal 類別目錄 (p, s) decimal(38, s) money 和 smallmoney 類別目錄 money float 和 real 類別目錄 float

註：SQL SERVER 2005 http://technet.microsoft.com/zh-tw/library/ms187810%28SQL.90%29.aspx

SQL SERVER 2008: http://technet.microsoft.com/zh-tw/library/ms187810.aspx

對於SQL SERVER 2005（含以前）版本若運算結果為整數類別則均傳回int，當運算結果為bigint，而傳回類型卻仍定義為int，因而造成溢位錯誤，就SQL SERVER 2008文件上說明此版本已修正此限制，但實際測試時卻仍發生相同問題。

函數引數（Argument）問題

以RADIANS函數將角度（deg）30度轉換為弧度（rad），傳入引數（Argument）將影響傳回結果，官方文件定義傳回資料型態類型與引數相同，此與一般概念所差異，使用上請多留意。測試如下所示。

測試		RADIANS語法
指令 結果 RADIANS(30) 0 RADIANS(30.0) 0.523598775598298790		RADIANS ( numeric_expression ) 傳回類型 傳回與 numeric_expression 相同的類型

l   當傳入30.0（浮點數）時函數傳回值為0.5236（浮點數）

l   當傳入30（整數）時傳回則為0（整數）

綜合前述SQL SERVER中數學運算時可能發生溢位或捨位的問題，整理出下列說明。

1.  運算元兩者中擇高優先順序

當運算子為乘法時，因運算元均為整數而可能發生溢位的問題。當為除法時，會誤將商數當成一般運算結果，而發生捨位誤差的問題。當運算子均為相同優先順序時，運算會自左而右依序運算的特性，值得特別注意。

A	*	B	=	R
	運算子			資料型態由A及B兩者中擇高優先順序的資料型態。

2.  引數

對於許多函數如RADIANS、DEGREES等，傳回資料型態是由引數所決定，因此，呼叫時需特別將引數轉換成輸出結果預期的資料型態。

RADIANS	(	A	)	=	R
函數		引數			資料型態由引數所決定

3.  運算式結果

在SQL SERVER 2005（含）以前版本，許多函數運算結果若為整數類別時，回傳僅以int代表，當運算結果已達bigint範圍將造成溢位錯誤。雖2008版本官方文件說明已修正此限制，但實測上仍相同。

SUM	(	A	)	=	R
函數		引數			資料型態由運算式結果所決定，但回傳整數類別時僅以int代表

數學運算上許多不可預期的錯誤或難以除錯的問題，常來自於前述三個SQL SERVER的運算特性，開發或系統測試時需多加注意。

---

[1]資料來源：http://technet.microsoft.com/zh-tw/library/ms175009.aspx

[2]資料來源：http://technet.microsoft.com/zh-tw/library/ms190309.aspx