Head in the Cloud

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)

Microsoft SQL Server, as of version 9.0 (2005), allows you to cheat the max row size limit of 8K. If we have a row of variable length data types (ie. varchar), the total byte count can be more than 8K. SQL Server will magically spill the data over to the next page. Cool right? Well... yes and no. Cool if your hands are tied and it gets you out of a jam. Not cool if you care about database performance and scalability. Having a row span more than one page (in Oracle we call them blocks), results in page (or block) chaining. The overhead involved in block chaining can cause some significant performance hits depending on how often it happens, size of the table, fragmentation, etc. This goes for most any popular RDBMS. Let's look at Oracle (no point in just picking on SQL Server). Oracle allows a 64K max row size (and that's a hard limit... no loosy goosy there). However, block size is determined by the value of the db_block_size init parameter set during database cre...