Geek Answers Handbook

Why are SQL field lengths always (2 ^ n) -1, if not less than 127?

Many database schemas seem to follow the following standard:

(2 ^ n) -1 for large fields:

varchar(511) varchar(255) varchar(127)

... then (2 ^ n) for smaller

 varchar(64) varchar(32) varchar(16) varchar(8)

I understand why the numbers (2 ^ n) -1 are used, so I do not understand why there is no need to continue the trend down to small fields.

eg.

 varchar(63) varchar(31) varchar(15) varchar(7)

Is there a reason for this, or is it just that profitability has declined too much?

+6
database varchar schema
source share
7 answers

I remember the old days when using 2 ^ n Length was better than aligning blocks on disk or in memory. The aligned block was faster. Today, the size of the “block” is larger, and the memory and disk are fast enough to ignore alignment, with the exception of the very large blocks that are. (Whatever "very big" means today ...)

Nowadays, it's just traditional.

And another reason for my famous saying: There are only 10 types of people: those who can be binary and others.

And 2 ^ n -1 are candidates for Mersensky primes. so its geeky too ...

+4
source share

I think that just programmers choose a number from the air when it does not really matter.

If you needed to set a limit on the “notes” field, for example, a non-programmer would probably choose 100 or 200 characters as a round number, whereas a programmer might think of 127 or 255 as a round number.

+7
source share

I can’t say how I saw the “-1” trend, used differently for smaller fields. However, I think that all this is nothing but the / dba OCD developer. Always wanting “round” numbers when they really need to make decisions about the length of the field based on business rules, not “prettiness”

+3
source share

Not only are revenue reduced, but if you specify line types with arbitrary restrictions, you are more likely to cause a problem, not solve it.

If this is truly a business constraint, use the TEXT type (or similar) and the CHECK constraint for the length and select the number according to the business constraint you are trying to enforce.

The DBMS will probably use storage for the TEXT type in the same way for varchar (x). If this is not the case, and you do not care, you should study the internal storage strategies of your specific system and consider whether there is any benefit for the cook or not.

+2
source share

Actually, this is the first time I heard about such a "trend." My varchar fields are always as long as I need them.

I would say that there is no particular reason. Just a developer preference.

0
source share

I can’t imagine for a moment why they determine the size of the fields on any account 2 ^ N or 2 ^ N-1. From a SQL Server point of view, this sounds more like a misconception about how SQL stores this data at the page level than for a specific reason.

I would pay more attention to types, potential page storage (SLoB / LoB) and rows / page + that meet business needs with a size than a formula based on using 2 ^ N, etc.

0
source share

if not less than 127? I think 1 byte = 8 bits (-128, 127) 127 is the limit. Does anybody agree

0
source share

More articles:


All Articles