0 votes
1 view
in SQL by (20.3k points)

You often see database fields set to have a magnitude of 255 characters, what is the traditional/historic reason why? I assume it's something to do with paging/memory limits, and performance but the distinction between 255 and 256 has always confused me.

varchar(255)

Considering this is a capacity or magnitude, not an indexer, why is 255 preferred over 256? Is a byte reserved for some purpose (terminator or null or something)?

Presumably varchar(0) is a nonsense (has zero capacity)? In which case, 2^8 of space should be 256 surely?

Are there other magnitudes that provide performance benefits? For example is varchar(512) less performant than varchar(511) or varchar(510)?

Is this value the same for all relations databases, old and new?

disclaimer - I'm a developer, not a DBA, I use field sizes and types that suit my business logic where that is known, but I'd like to know the historic reason for this preference, even if it's no longer relevant (but even more if it still is relevant).

Edit:

Thanks for the answers, there seems to be some consensus that a byte is used to store size, but this doesn't settle the matter definitively in my mind.

If the metadata (string length) is stored in the same contiguous memory/disk, it makes some sense. 1 byte of metadata and 255 bytes of string data, would suit each other very nicely, and fit into 256 contiguous bytes of storage, which presumably is neat and tidy.

But...If the metadata (string length) is stored separately from the actual string data (in a master table perhaps), then to constrain the length of string's data by one byte, just because it's easier to store only a 1-byte integer of metadata seems a bit odd.

In both cases, it would seem to be a subtlety that probably depends on the DB implementation. The practice of using 255 seems pretty widespread, so someone somewhere must have argued a good case for it in the beginning, can anyone remember what that case was/is? Programmers won't adopt any new practice without a reason, and this must have been new once.

1 Answer

0 votes
by (36.7k points)

With the most length of 255 characters, the DBMS will like better to use one computer memory unit to point the length of the info within the field. 

If the limit were 256 or larger, 2 bytes would be required.

A value of length zero is definitely valid for varchar knowledge (unless affected otherwise). 

Most systems treat such associate empty string as distinct from NULL, however, some systems (notably Oracle) treat associate empty string identically to NULL. 

For systems wherever associate empty string isn't NULL, a further bit somewhere within the row would be required to point whether or not the worth ought to be thought-about NULL or not.

  As you note, this can be a historical improvement and is maybe not relevant to most systems nowadays.

...