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

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

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?

(30 Aug '10, 10:42) Volker Barth

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.

permanent link

answered 06 May '11, 11:32

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.5k41735
accept rate: 22%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×145

question asked: 30 Aug '10, 10:27

question was seen: 862 times

last updated: 06 May '11, 11:32