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...

A lot of ground was covered in connecting our TellerUI to the database. We implemented FinderMethods in our Customer class that return an IList back to the UI to be bound to the results grid. We also implemented an App.config file to store our connection string. While we did get the code working, it was not as expected. We originally implemented it to use the same connection for all operations throughout the find and load process, so we we're not opening and closing connections over and over; a very costly operation. However, this caused a runtime error. The connection would not allow multiple data readers to be open at a time. Our stop gap solution to get the app working was to simple use a new connection for every call to the database. I spent some time debugging the original design. The solution was a setting in the connection string-- MultipleActiveResultSets=True --which allows us to have multiple resultsets open at a time in SQL Server 2005 and 2008. The new connectio...