SQL Prompt使用教程：使用Float或Real數(shù)據(jù)類型的危險

---

SQL Prompt根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。本文介紹了使用Float或Real數(shù)據(jù)類型的危險。

點擊下載SQL Prompt正式版

---

浮點數(shù)據(jù)類型可容納非常大的數(shù)字，但是缺少了精度。它們對于某些類型的科學(xué)計算很方便，但是在更廣泛地使用時很危險，因為它們會引入較大的舍入誤差。

浮點運算就是為了避免計算中的溢出錯誤而容忍和管理近似。在現(xiàn)實世界中，我們通常關(guān)心數(shù)字的準確性，而會犧牲空間和資源以避免溢出。

科學(xué)是在誤差范圍內(nèi)工作的，而精確在商業(yè)會計中至關(guān)重要。當我還是一名初級程序員時，我曾經(jīng)寫過一種我認為是銀行計算交易利潤非常合適的方法。在一百萬英鎊中，最多也就一兩便士的誤差，我很滿意。它使用了我們當時用來開發(fā)財務(wù)軟件包的PL / 1編譯器中固有的計算。我向他們展示了精心制作的應(yīng)用程序，他們感到震驚。冷酷無情的銀行家們毫不留情地表示一百萬英鎊沒了幾分錢。他們不會接受的。我被迫用精確的匯編代碼編寫一個精確的二進制編碼的十進制（BCD）程序包。

SQL Prompt具有代碼分析規(guī)則（BP023），該規(guī)則將提醒您使用Float或Real數(shù)據(jù)類型，這是因為它們可能會引入許多組織通常在其SQL Server數(shù)據(jù)上常規(guī)執(zhí)行的那種計算方式。

近似數(shù)的數(shù)據(jù)類型

浮點運算是在優(yōu)先考慮節(jié)省內(nèi)存的同時，提供了一種涉及大量運算的通用方法的時代設(shè)計出來的。盡管它對于許多類型的科學(xué)計算（尤其是那些符合浮點算術(shù)雙精度IEEE 754標準的科學(xué)計算）仍然有用，但它必然是一種折衷方案。線索就是這種數(shù)據(jù)和算術(shù)的名稱：“近似”。浮點數(shù)不能精確表示所有實數(shù)：此外，浮點運算不能精確表示所有算術(shù)運算。但是，即使不總是精確地保留數(shù)字，它們可以保留的數(shù)字的幅度范圍也遠大于其他數(shù)字類型。

使用浮點運算引起的問題是由于復(fù)雜計算過程中的四舍五入而引起的，如果數(shù)據(jù)處于“不良條件”狀態(tài)，則最常見的問題就是輸入中的細微變化會在輸出中放大。隨著數(shù)字表示精度的提高，這種不精確性已經(jīng)不那么明顯了，但是它們?nèi)匀淮嬖?。在使用有效但不能用浮點數(shù)表示的數(shù)字時，還存在一些深奧的限制，例如tan（π/ 2），但這些可能僅會激發(fā)數(shù)學(xué)家的興趣。

SQL Server浮點數(shù)據(jù)類型

SQL標準具有三個浮點，近似數(shù)據(jù)類型、REAL、DOUBLE PRECISION和FLOAT（n）。 SQL Server符合此要求，只是它沒有DOUBLE PRECISION數(shù)據(jù)類型，而改用FLOAT（53）。 FLOAT（24）和FLOAT（53）數(shù)據(jù)類型對應(yīng)于IEEE 754標準中的Binary32（Single）和Binary64（double），并分別存儲在4和8字節(jié)中，并分別保留7和16位數(shù)字。當計算產(chǎn)生與使用還使用IEEE 754的.NET框架的應(yīng)用程序相同的結(jié)果很重要時，它們很有用。當數(shù)字的大小超過DECIMAL數(shù)據(jù)類型所允許的最大值（38位）時，還需要雙精度類型，但精度下降。當然，近似數(shù)不能可靠地用于任何相等性檢驗中，例如WHERE子句。

使用REAL數(shù)據(jù)類型的計算（單精度）

我將嘗試REAL數(shù)據(jù)類型。FLOAT(24)數(shù)據(jù)類型或更小的數(shù)據(jù)類型以相同的方式反應(yīng)。在SQL Server中使用浮點數(shù)進行實驗時，要記住的第一件事是，SSMS以掩蓋微小差異的方式呈現(xiàn)浮點數(shù)。例如：

SELECT Convert(REAL,0.100000001490116119384765625)

…得到0.1

為了更準確地看到浮點數(shù)中存儲了什么值，您必須使用STR（）函數(shù)，指定實際需要的精度。

這已經(jīng)令人擔憂。畢竟，我們正在處理具有數(shù)百萬行的數(shù)據(jù)，因此，除非像“銀行家四舍五入”之類的結(jié)果取平均值，否則小錯誤就會堆積起來。這個錯誤已經(jīng)接近我在引言中提到的“百萬英鎊的便士”（1/240000000）！

讓我們避免使用0.1，并將其歸結(jié)為奇怪的浮點數(shù)。1除以3怎么樣？這肯定不是問題吧？

哎呀，它錯了。好的，這是一個很小的錯誤，但請記住我關(guān)于銀行家的故事。答案是對還是錯，穿著灰色西裝的男人沒有灰色陰影。在商學(xué)院，只有一個標記和一個叉。沒有表示“足夠近”的標志。

一個簡單的測試是將數(shù)字1除以1到20。會出什么問題呢？

我們可以存儲浮點數(shù)和數(shù)值計算的結(jié)果，將它們都轉(zhuǎn)換為字符串，然后比較字符串（請注意，字符串STR()可以放在前導(dǎo)空格中，這會使情況變得復(fù)雜）。

現(xiàn)在，如果我們列出那些數(shù)字不匹配的行呢？

啊! 只有在除數(shù)為1、2、4、8或16的情況下，結(jié)果才正確。

如果您希望某種程度上的浮點數(shù)是準確的，而數(shù)值版本卻不正確，則以下是在Excel中計算出的數(shù)值商：

使用FLOAT（25）或更高（雙精度）的計算

如果使用雙精度浮點數(shù)FLOAT(25)或更高的精度，則所有測試都將通過，因為STR()函數(shù)最多允許小數(shù)點右邊16位。如果大于16，則結(jié)果將被截斷。雙精度數(shù)據(jù)類型具有16位數(shù)字，而單精度數(shù)據(jù)類型具有7位數(shù)字。您還將看到單精度數(shù)據(jù)類型正確獲取了前七個數(shù)字。同樣，雙精度會正確獲取前16位數(shù)字。我們可以擴大數(shù)字以查看近似值。

DECLARE @FirstApproximate FLOAT(53) = 10000000000000000.1
SELECT Str(@FirstApproximate,40,16) AS BigNumberWithaDecimal

那小部分消失了，不是嗎？這可能只是微小的差異，但是在某些計算中，它可能會引起問題。

結(jié)論

浮點算法在存儲上既快速又經(jīng)濟，但提供了近似的結(jié)果。它適用于條件良好的科學(xué)應(yīng)用，但不適用于財務(wù)計算，因為財務(wù)計算要求數(shù)字是“正確”或“錯誤”。它在數(shù)據(jù)庫中還具有額外的缺點，因為您不能可靠且一致地測試兩個近似數(shù)是否相等。

說永遠不要在SQL數(shù)據(jù)類型或算術(shù)中使用浮點數(shù)是不正確的。在SQL標準中，有一個特定的近似類型。如今，在有適當要求的SQL Server中，我始終堅持使用雙精度浮點數(shù)據(jù)類型。它們非常適合用于建模天氣系統(tǒng)或繪制軌跡等目的，但不適用于普通組織可能使用數(shù)據(jù)庫的計算類型。

如果發(fā)現(xiàn)錯誤使用了這些類型，則應(yīng)改用合適的DECIMAL/ NUMERIC類型。如果您知道需要浮點算法并可以解釋原因，那么您可能足夠了解避免浮點的陷阱。

本教程內(nèi)容就是這樣了，希望對您有所幫助~您可以下載SQL Prompt試用版嘗試一下~

相關(guān)內(nèi)容推薦：

SQL Prompt 使用教程>>>

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點擊【咨詢在線客服】