Posts

Showing posts with the label datatypes

Never use float for money

The web is riddled with articles on this very topic, yet it bares repeating: never use float for money. I repeat: never never never never never use a floating point data type to represent monetary values or do financial math. Why? Single and Double precision floating point numbers are not accurate. Ironically, it's by design. They are optimized for performance where absolute accuracy is not a concern. Microsoft talks about it briefly here:  https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/floating-point-numeric-types [...]there's no double or float instance that exactly represents 0.1. Because of this difference in numeric types, unexpected rounding errors can occur in arithmetic calculations when you use double or float for decimal data. Baeldung has an excellent writeup on the science behind why floats behave the way they do:  https://www.baeldung.com/cs/floating-point-numbers-inaccuracy So what should we use for money and financial calculation...

Unsigned Integers in SQL Server, Oracle, MySQL, Postgres, and DB2

A recent project required the need to store an unsigned 64-bit integer in a SQL Server database table. BIGINT won't cut it because BIGINT has a max value of 9,223,372,036,854,775,807 (signed 64-bit integer), and the unsigned 64-bit integer's max value is 18,446,744,073,709,551,615.  The solution is to use NUMERIC(20) instead. I recalled that other DBMS's do support unsigned integers as integer types vs. coercion with numeric, so I decided to compare a few of them and document them here. DBMS Unsigned 64Bit Unsigned 32Bit Unsigned 16Bit SQL Server (as of "Denali") numeric(20) numeric(10) numeric(5) Oracle (as of 11g) number(20), numeric(20) number(10), numeric(10) number(5), numeric(5) MySQL (as of 5.x) bigint, numeric(20) int, numeric(10) smallint, numeric(5) Postgres (as of 9.x) numeric(20) numeric(10) numeric(5) DB2 UDB (as of 9.x) numeric(20) numeric(10) numeric(5)