Consider the case where business-related VARCHAR strings of length 5 through 20 make the best candidates for various primary and foreign keys in a handheld UltraLite database, from application programming and database maintenance points of view. Most tables will be read-only, one will be mostly inserted, some queries and updates.
Are there any overwhelmingly compelling performance reasons to add artificial (surrogate) columns to the tables, and use those columns as the physical keys instead?
I'm guessing "no", but guesswork doesn't qualify as "due diligence" :)
asked 30 Aug '10, 10:27
If the column values are all very similar, ie 'CUSTxxxxx', you would need to have an appropriately large max hash size on the indexes to speed up searching with those values (for 'CUSTxxxxx', first 4 bytes of the hash will always be the same and therefore wasted). Larger hash size means less entries per index page. You also have to balance the cost in space of the extra column.
If the data is well distributed in the first few bytes, then the VARCHAR column would be just as efficient.
answered 06 May '11, 11:32