Il y a une vieille blague sur l’arithmétique à virgule flottante:

« Si je coupe un gâteau en trois, chaque morceau représente 0,33 du gâteau. Si je colle les trois morceaux ensemble, cela me donne 0,99 du gâteau. Où est passé le reste du gâteau ? »
— » Simple. C’est le petit bout coincé sur le couteau ”

L’arithmétique à virgule flottante consiste à tolérer et à gérer l’approximation afin d’éviter les erreurs de débordement dans les calculs. Dans le monde réel, nous nous soucions généralement de la précision des chiffres et nous sacrifierons plutôt de l’espace et des ressources afin d’éviter les débordements.

Alors que la science fonctionne avec bonheur dans une marge d’erreur, la précision est importante en comptabilité d’entreprise. Quand j’étais programmeur cub, j’ai écrit une fois ce que je pensais être un moyen parfaitement approprié de calculer le profit des transactions boursières. Dans un million de livres, c’était un sou ou deux au plus. J’étais bien content. Il a utilisé les calculs inhérents au compilateur PL / 1 que nous utilisions à l’époque pour développer des montages financiers. Je leur ai montré l’application finement conçue, et ils ont été horrifiés. Un sou dans un million de livres semblait aux commerçants de la ville durs à cuire être imprudents. Ils ne l’auraient pas. J’ai été obligé d’écrire un paquet binaire-codé-décimal (BCD) en code assembleur qui était précisément précis.

L’invite SQL a une règle d’analyse de code (BP023) qui vous avertira de l’utilisation de types de données FLOAT ou REAL, en raison des inexactitudes importantes qu’ils peuvent introduire dans le type de calculs que de nombreuses organisations effectueront régulièrement sur leurs données SQL Server.

Types de données à nombres approximatifs

L’arithmétique à virgule flottante a été conçue à une époque où il était prioritaire d’économiser de la mémoire tout en offrant une manière polyvalente de faire des calculs impliquant de grands nombres. Bien qu’il soit encore utile pour de nombreux types de calculs scientifiques, en particulier ceux conformes à la norme IEEE 754 à double précision pour l’arithmétique à virgule flottante, il s’agit nécessairement d’un compromis. L’indice est dans le nom de ce type de données et d’arithmétique‘ « approximatif ». Les nombres à virgule flottante ne peuvent pas représenter avec précision tous les nombres réels: de plus, les opérations à virgule flottante ne peuvent pas représenter avec précision toutes les opérations arithmétiques. Cependant, la plage de grandeur du nombre qu’ils peuvent contenir est beaucoup plus grande que ce qui est possible dans d’autres types numériques, même s’il n’est pas toujours tenu avec précision.

Les problèmes qui découlent de l’utilisation de calculs à virgule flottante sont dus à l’arrondi lors de calculs complexes, et sont le plus souvent observés si les données sont « mal conditionnées », de sorte que de petits changements d’entrée sont amplifiés dans la sortie. Les inexactitudes sont beaucoup moins apparentes avec une précision accrue de la représentation des nombres, mais elles sont néanmoins toujours présentes. Il existe également des restrictions ésotériques dans l’utilisation de nombres valides mais ne pouvant être représentés en virgule flottante, telles que tan (π / 2), mais celles-ci ne sont susceptibles d’exciter que les mathématiciens.

Types de données à virgule flottante SQL Server

La norme SQL a trois types de données approximatifs à virgule flottante, REALDOUBLEPRECISION et FLOAT(n). SQL Server est conforme à cela, sauf qu’il n’a pas de type de données DOUBLEPRECISION, en utilisant FLOAT(53) à la place. Les types de données FLOAT(24) et FLOAT(53) correspondent à Binary32 (Simple) et Binary64 (double) dans la norme IEEE 754, et sont stockés en 4 et 8 octets, et 7 et 16 chiffres sont conservés, en conséquence. Ils sont utiles lorsqu’il est important que les calculs produisent le même résultat qu’une application utilisant le framework .NET qui utilise également IEEE 754. Le type de double précision est également requis lorsque les nombres dépassent dans leur grandeur le maximum autorisé par le type de données DECIMAL (38 chiffres) mais avec une perte de précision. Bien entendu, les nombres approximatifs ne peuvent être utilisés de manière fiable dans aucun test d’égalité, tel qu’une clause WHERE.

Calculs utilisant le type de données RÉEL (précision unique)

Je vais essayer le type de données REAL. Le type de données FLOAT(24), ou plus petit, réagit de la même manière. La première chose à retenir lors de l’expérimentation de nombres à virgule flottante dans SQL Server est que SSMS rend un nombre à virgule flottante d’une manière qui masque de petites différences. Par exemple:

gives donne 0.1

Pour voir plus précisément quelle valeur est stockée dans un nombre à virgule flottante, vous devez utiliser la fonction STR(), en spécifiant la précision que vous souhaitez réellement.

Déjà, c’est alarmant. Après tout, nous traitons des données avec des millions de lignes, de sorte que les petites erreurs s’accumuleront à moins que, comme les « arrondis des banquiers », elles ne se calculent en moyenne. Cette erreur est déjà proche du « penny en un million de livres » (1 / 240000000) que j’ai mentionné dans l’introduction!

Évitons 0.1 et réduisons-le à un monstre de virgule flottante. Que diriez-vous de diviser 1 par 3. Cela ne pourrait sûrement pas être un problème?

Oups. Il s’est trompé. OK, c’est une petite erreur mais rappelez-vous mon histoire sur les banquiers. Une réponse est soit juste, soit fausse, il n’y a pas de nuances de gris pour les hommes en costume gris. En école de commerce, il n’y a qu’une coche et une croix. Aucun signe qui signifie « assez près’.

Un test simple consiste à diviser un par des nombres de un à vingt. Qu’est-ce qui pourrait mal tourner?

Nous pouvons stocker les résultats du calcul en virgule flottante et numérique, tous deux convertis en chaînes et nous comparons ensuite les chaînes (attention que STR() peut mettre dans un espace de tête ce qui constitue une complication).

Now, what if we list the rows where the numbers don’t match?

Ugh! Ce n’est que lorsque le diviseur était 1, 2, 4, 8 ou 16 qu’il y avait un résultat correct.

Si vous espérez que le float était exact et que la version numérique ne l’était pas, voici le quotient numérique calculé dans Excel:

Calculs utilisant FLOAT(25) ou plus (double précision)

Si vous utilisez une virgule flottante à double précision, FLOAT(25)ou plus, les tests sont tous réussis, car la fonction STR() permet un maximum de seize places à droite de la virgule décimale. S’il y en a plus de 16, le résultat est tronqué. Le type de données à double précision a seize chiffres, tandis que le type de données à simple précision en a sept. Vous aurez également vu que le type de données de précision unique obtient les sept premiers chiffres correctement. De même, la double précision obtient les seize premiers chiffres correctement. Nous pouvons simplement élargir le nombre pour voir l’approximation.

Cette partie fractionnaire a disparu, n’est-ce pas? Ce n’est probablement qu’une petite différence, mais dans certains calculs, cela peut poser des problèmes.

Conclusion

L’arithmétique à virgule flottante est rapide et économique sur le stockage mais fournit un résultat approximatif. Il convient aux applications scientifiques bien conditionnées, mais pas aux calculs financiers, qui exigent qu’un nombre soit « juste » ou « faux ». Il présente également l’inconvénient supplémentaire dans une base de données car vous ne pouvez pas tester de manière fiable et cohérente deux nombres approximatifs pour l’égalité.

Il n’est pas correct de dire que vous ne devriez jamais utiliser de nombres à virgule flottante dans des types de données SQL ou en arithmétique. Les types approximatifs sont là dans la norme SQL dans un but. De nos jours, je m’en tiendrais toujours au type de données à virgule flottante à double précision dans SQL Server, où il existe une exigence appropriée. Ils sont parfaits à des fins telles que la modélisation de systèmes météorologiques ou le tracé de trajectoires, mais pas pour les types de calculs pour lesquels l’organisation moyenne est susceptible d’utiliser une base de données.

Si vous repérez une utilisation erronée de ces types, vous devez passer à un type DECIMALNUMERIC approprié à la place. Si vous savez que vous avez besoin d’arithmétique en virgule flottante et que vous pouvez expliquer pourquoi, vous en savez probablement assez pour éviter les pièges de la virgule flottante, comme celui qui s’est produit lors de la célèbre défaillance du missile Patriot qui a directement causé la mort de 28.