One of the things you just need to know about Oracle …

A couple of years ago I did ask my developers to start using the varchar2(char) option when creating new tables. In Norway we have some characters in the alphabet (‘Æ’, ‘Ø’, ‘Å’) that uses more than 1 byte of storage (when the database characterset is set to AL32UTF8). Using the default – varchar2(byte) – could then decrease the number of characters we actually end up storing in the database (when storing Norwegian text). Today I just read a tweet from Jonathan Lewis, directing me to one of his older blog posts – “Just in case”. I try to read every blog post from Jonathan, but sometimes it’s hard to keep up. This one I just happened to miss – at least until today. You better not miss it!

The article discusses why you shouldn’t increase your column width (of a varchar2) “just in case”. In my case – it thought me why it was a stupid idea to use varchar2(char) as a default approach. Many varchar2 columns in our databases doesn’t store Norwegian text, and would never contain these variable byte-length characters, hence should never be defined with varchar2(char). I should have thought: “Oracle must have made ‘byte’ the default for a reason”.

Post a Comment

Your email is never published nor shared. Required fields are marked *