Z dokumentacji technet: ( https://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx )

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value. The float and real data types are known as approximate data types. Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value.

Sprawdzaliście dokładnie z jakiego typu danych korzystacie? Jesteście pewni swoich wyników?
Zobaczcie najprostszy przykład braku świadomości precyzji typów danych, dzielenie liczby 1 przez 2:
SELECT 1/2
Wszyscy obeznani z tematem wiedzą, że wynikiem jest liczba 0. Nie wierzysz? Sprawdź sam!

---

Dla ciekawskich wyjaśnienie :
SQL, tak jak każdy system informatyczny, ma swoje metody na przechowywanie liczb. Jak pamiętamy z matematyki liczby rzeczywiste zawierają przypadki, co do których należy stosować pewien kompromis ponieważ nie jesteśmy w stanie zapisac ich w krótkiej lub w ogóle skończonej formie (zwłaszcza liczby przestępne jak pi).
Dlatego zostały przygotowane specjalne różne typy danych do przechowywania liczb: float/real (real jest aliasem do float(24)), decimal/numeric (numeric jest aliasem do decimal), money, smallmoney, bigint, int, smallint,tinyint,bit.

Każdy z nich przechowuje liczby w inny sposób, główną charakterystyką jest ich różnica w maksymalnej precyzji oraz wartości min i max (co w efekcie końcowym odbija się na wielkości, czyli ilości miejsca zajętego w pamięci i na dysku). Dlatego obliczenia wykonywane na nich są również skracane i prezentowane w taki sposób jak liczby wejściowe. To niejawna konwersja powoduje, że prawidłowy wynik dzielenia czyli 0.5 sprowadzany jest do INTa. To tak samo jak odpalilibyśmy zapytanie: SELECT CAST(0.5 AS INT)
Rozwiązaniem tego problemu jest celowe użycie separatora dziesiętnego lub cast jednej z liczb do „szerszego” formatu.

SELECT 1.0 /2
SELECT 1 / 2.0
SELECT CAST(1 AS decimal(10,2)) / 2

 
We wszystkich tych przypadkach wynik zostanie zaprezentowany prawidłowo. Oczywiście stosowanie „szerszych” typów nakazuje SQLowi operować z większą precyzją. Niestety są sytuacje, w których wykonywanie działań w połączeniu z float, który w praktyce ma mniejszą precyzję niż najszerszy decimal daje wynik zaprezentowany w … float. Ale o tym za chwilę..

---

Wracamy do naszego zagadnienia…
Temat „wycieku precyzji” jest często poruszany na blogach technicznych i dosyć często pomijany w praktyce. Konsekwencje natomiast mogą być spore, szczególnie tam gdzie precyzja ma ogromne znaczenie dla wyników końcowych.

Zasada jest prosta.
FLOAT – przybliża wartości, nie zapamiętuje ich dokładnej precyzji
DECIMAL – nie robi tego, co robi float 🙂 Ale to nie znaczy, że przy operacjach nie może nastąpić zaokrąglenie…

 
Jeden z najbardziej charakterystycznych przykładów wywołujących ciarki ;] jest stosunkowo prosty. W świadomości wielu problem z precyzją pojawia się dopiero przy operacjach dzielenia. BŁĄD. Dlaczego?
Mamy trzy liczby float.
Trzecia z nich jest sumą dwóch pierwszych.
Wykonujemy prostą operację. Od trzeciej odejmujemy pierwszą i drugą. Proste, prawda? Powinno wyjść 0. No to jazda:

DECLARE @Pierwsza float, @Druga float, @Trzecia float
SET @Pierwsza = 54;
SET @Druga = 0.03;
SET @Trzecia = 0 + @Pierwsza + @Druga;
SELECT @Pierwsza as Pierwsza,@Druga as Druga,@Trzecia as Trzecia, @Trzecia - @Pierwsza - @Druga AS [@Trzecia - @Pierwsza - @Druga];

 

Dla pewności sprawdźmy co będzie, jeśli nasze liczby będą decimalami:

DECLARE @Pierwsza decimal(10,2), @Druga decimal(10,2), @Trzecia decimal(10,2)
SET @Pierwsza = 54;
SET @Druga = 0.03;
SET @Trzecia = 0 + @Pierwsza + @Druga;
SELECT @Pierwsza as Pierwsza,@Druga as Druga,@Trzecia as Trzecia, @Trzecia - @Pierwsza - @Druga AS [@Trzecia - @Pierwsza - @Druga];

 
Ale żeby nie było tak cudownie, przy operacjach dających wyniki z większą precyzją niż liczby wejściowe, utrata precyzji może doprowadzić do nieoczekiwanych wyników (zawyżonych/zaniżonych)

Mamy trzy liczby decimal(8,4). Trzecia z nich powstała w wyniku pomnożenia jedynki i dwóch pierwszych.
Co otrzymamy w wyniku podzielenia trzeciej przez wynik mnożenia dwóch pierwszych? Powinno wyjść 1. Otóż nie!

DECLARE @Liczba1 decimal(8,4), @Liczba2 decimal(8,4), @Liczba3 decimal(8,4);
SET @Liczba1 = 54;
SET @Liczba2 = 0.03;
SET @Liczba3 = 1 * @Liczba1 / @Liczba2;
SELECT @Liczba1 as [@Liczba1],@Liczba2 as [@Liczba2],@Liczba3 as [@Liczba3], @Liczba3 / @Liczba1 * @Liczba2 AS [@Liczba3 / @Liczba1 * @Liczba2]

 

 
Co się stanie jeśli zamienimy liczbę @Liczba2 na 0.003 ?

 

Ale prócz wyniku i NULL dostaliśmy komunikat:

Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting numeric to data type numeric.

 

Ciekawie, prawda?

A co jeśli nie używacie float? „Nie ma” problemu, wystarczy że użyjecie jednej z poniższych funkcji i już wszystkie Wasze obliczenia będą wykonywane w pamięci jako float!!!

ACOS
ASIN
ATAN
ATN2
COS
COT
EXP
LOG
LOG10
PI
RAND
SIN
SQRT
SQUARE
TAN
Dlaczego? Ponieważ float ma wyższy priorytet niż nawet największy decimal. Jeśli pojawi się choć jedna taka liczba w Waszych wyliczeniach – skutki mogą być takie jak w przykładach powyżej. To jakiego typu jest wynik można sprawdzić przy użyciu specjalnej funkcji SQL_VARIANT_PROPERTY:

DECLARE @Liczba1 decimal(38,10) = 4
DECLARE @Liczba2 float = 1

SELECT SQL_VARIANT_PROPERTY ( @Liczba1 * @Liczba2, 'BaseType' )
SELECT SQL_VARIANT_PROPERTY ( @Liczba1 + @Liczba2, 'BaseType' )
SELECT SQL_VARIANT_PROPERTY ( RAND(), 'BaseType' )

 

Powodzenia!

 

POST PIERWOTNIE OPUBLIKOWANY JAKO TSQL NA DZIŚ #29