Unsigned Integers in SQL Server, Oracle, MySQL, Postgres, and DB2
• Jason M Penniman
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)|