[MSSQL] SQL的數值運算

Visits: 1

SQL Server在作四則運算、函數運算時,會根據給定的引數而決定回傳的數值型態,在運算過程中需特別注意資料類型優先順序,否則將出現溢位(Overflow)、捨位誤差等。

捨位誤差

如果整數(int)除以整數,結果就是整數,任何小數點後數字部分都會截斷。因此若要計算出含小數位的數值,必須將運算過程中的其中一項數值轉換為可包含小數位的型別(float, decimal),因此在整個運算過程中,就會因為小數位資料型別的資料類型優先順序較高,而得出含小數位的值。

資料類型優先順序

當一個運算子結合不同資料類型的運算式時,低優先順序資料類型會先轉換為高優先順序資料類型,請參見下表

1. 使用者自訂資料類型 (最高)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (包含 nvarchar(max))
26. nchar
27. varchar (包含 varchar(max))
28. char
29. varbinary (包含 varbinary(max))
30. binary (最低)
實例說明
print(8 / 4) --2
print(8.0 / 4) --2.000000
print(8 / 4.0) --2.000000
print(convert(int, 8.0) / 4) --2
print(8 / 4 * convert(decimal(6,2), 1)) --2.00
  1. 範例1中,兩者都是整數(int),直接運算後得到整數
  2. 範例2中,前者為浮點數(float),根據文件,float的順序(10)比int的順序(16)高,因此運算結果會轉為float的形式
  3. 同範例2
  4. 將float以convert轉換後,同樣皆為int,得到結果為int
  5. 8/4為int,乘上轉換成1.00的數值後,因為decimal的順序(12)比int的順序(16)高,因此運算結果為decimal型式

溢位問題

每種數值型別都有最大的可儲存空間,若超過,則會發生溢位問題。

數值型別的最大儲存空間

decimal(Precision, Scale): p = 38, s = 38
float(n): n = 53
int: 2^31 - 1 (2,147,483,647)
bigint: 2^63 - 1 (9,223,372,036,854,775,807)
smallint: 2^15 - 1 (32,767)
tinyint: 255
實例說明
print(power(2, 30)) --1073741824
print(power(2,30) + power(2,30))  --算數溢位錯誤
print(power(2,30) + power(2,30) - 1) --算數溢位錯誤
print(power(2,30) - 1 + power(2,30)) --2147483647
  1. 2^30可被int儲存
  2. 2^30 + 2^30 > int的最大儲存空間,噴錯
  3. 因為2^30 + 2^30已超過最大儲存空間,一樣噴錯
  4. 2^30 – 1 + 2^30,剛好在最大儲存空間

參考文獻

部落格
官方文件-資料類型優先順序
數值類型

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

You may also like these