Are there compelling performance reasons to use integer surrogate keys in UltraLite?

[Breck_Carter]

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" 🙂

[VolkerBarth]

What about character set issues? Might there be cases where VARCHARs have to be converted between database server and clients (with probably small performane influences) or cases where data from different hndheld databases with different charsets have to be consolidated in one central database?

[philippefbertrand]

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.