Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

In database schema's, I often notice VARCHAR sizes are rounded to the byte offsets 128/256 or 4096. I've done it before as well, and the idea behind it was probably something with efficiency.

However, is there still a valid reason to do so nowadays? I often use '50', '100' or '200' as VARCHAR sizes these days, as they are more natural and typically also shown in validation checks to the user.

share|improve this question
1  
Older programmers are often so used to work with powers of two, that they may simply consider 128/256/4096 more natural. There may not be any performance reason at all. –  Jan Hudec Nov 29 '11 at 13:26
1  
Whether there's any efficiency advantages may depend on which individual database is used. MySQL and DB2 are implemented very differently. –  David Thornley Nov 29 '11 at 14:26

5 Answers 5

The only rational explanation I can think of would be: If the DBMS stores the values of a column sequentially, and the sizes are not rounded to a power of 2, then some elements may have to be "split" into two pages on the hard drive (e.g. the first 10 bytes in page n and the next 40 bytes in page n+1), which might in some cases lead to two reads from the hard drive instead of one.

More likely is @Jan Hudec's point that, many programmers think of "128" or "256" as "nice round numbers", which makes them a more natural choices than odd numbers like 137, 19 or 100.

share|improve this answer
    
"Many programmers think of 128 or 256 as nice round numbers". We are indeed absolute freaks. :-) –  Konamiman Nov 29 '11 at 14:14
1  
Note that you need at least a byte to store the length of the data, so if your first explanation were true, we'd see a lot of limits of 31, 63, 127, 255, or 510 bytes. –  dan04 Nov 29 '11 at 14:38
    
1 byte to indicate length would allow for strings of up to 255 (not 256) characters. SQL Server, and I'd guess most other systems, uses two bytes. –  Philip Kelley Dec 13 '11 at 14:54

In general there is no reason for those column lengths. There will be no performance improvement of a varchar(100) column versus a varchar(128) column.

However, I would double check the database system you are using for further clarification on restrictions and other vendor specific caveats.

For example, here's a good example of a database system restriction for SQL Server:

http://msdn.microsoft.com/en-us/library/ms186981.aspx

The total length of the row is more important than individual column lengths.

share|improve this answer

I don't recall whether it was a DBMS or compiler, but I do recall (a long time ago) learning to use powers of 2 for array and column lengths. There was a justification that it was 'faster' because of the implementation could use bit shifting. Whether holds true anymore is an open question. Anyone have any idea on whether it is still valid?

BTW I've moved column widths to uniform number b/c it is strange to tell users the char limit is 256 chars.

And some very old databases did limit you to 256 char-width columns.

share|improve this answer

It probably doesn't really matter, since you'd really only see some storage efficiency if the size of your entire row happened to be a power of 2. It's possible that sticking with powers of 2 could make it more likely that your row size would work out to a power of two (since most native data types tend to be power-of-2-sized [depending on database]), but I wouldn't make it a hard-and-fast rule.

It might make more sense if you were working with large (4K or larger) columns, since those could possibly be stored separately, and sizing them so that they fit within one storage block (whatever your database uses for on-disk storage) would gain you something.

share|improve this answer

While I'm not familiar with all DBMS systems, the smallest "physical" unit of storage in Oracle is a "block" which by default is 2KB in size. The practice of sizing your columns in powers of two is part of a larger practice of sizing your rows to fit properly in storage blocks. Sizing your columns so one row would require one byte more than the block-size would require two blocks to be allocated and your row would also span two blocks, making reading, inserting and scanning more time consuming than if you could fit each row one block (and only have one row in each block). That, at least, is the historic reason for it. Nowadays, most people consider this practice to be sub-optimization.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.