Optimization should only be done if it is really very necessary. I doubt that this optimization is worth in any scenario.
The basic rule for selecting the type when they can be confused is what you will use them for. Numbers represent quantities. CPF or CNPJ are quantities? No, they are identifiers that can even change, that can one day have letters (happened with RG). The correct semantics for this data is the varchar
after all he is descriptive.
The question of zero on the left alone is already good reason to use varchar
. So you can skulk around to solve a problem caused by the wrong guy. I always say this for everything in computing and even in life, the person causes a problem and then causes new ones trying to solve something that they should have done right from the start (This happens more than almost everyone realizes, especially with experienced people who can no longer see their mistakes as the arrogance that develops, even speaking the opposite)
You should always choose to varchar
on any given data until you find a reason to choose another type. A general ID, for example, has a good reason to be a int
, It needs to be incremented, you do accounts with it. The same goes for salary, price, quantity. But it is not valid for zip code, phone, even a property number in the address when this field is separated. An expiration has reason to be of another type, you need to sort the information by date order, and as text this would not work correctly. These are just a few examples.
Some will even say that it is necessary to use the varchar
because he has scores beyond numbers. But this is not a good argument since the punctuation should not even be recorded, at least not in most situations. This should be solved in the presentation only, the score is not part of the data (usually).
In some cases a numeric type cannot even be used since the number of digits used to identify something is greater than the numeric type holds.
Some will say that neither varchar
should be used. As the size is fixed, a simple char
solves the problem. But there are controversies about this. Depending on the DB system one type may actually be more advantageous than another but not enough to justify, in most cases a choice will be made because of the performance. Semantics should be the primary option.
Unfortunately there is a lot of wrong information on the internet about the subject (or any subject :) ).
I think the question can be answered objectively, so much so that I did. The fact of having another answer that only issues opinion is not the fault of the question.
– Maniero
I saw no value in the question at first, but the fact that there is a consideration that the answer is a matter of opinion made me realize that the question is useful yes (although the answer is obvious, it seems that it is not so obvious to everyone).
– Caffé
I’ve always used
VARCHAR
, but despite not believing anything that is on the internet, already some comments advocating to useINT
, although it does not present any foundation. And separating into: body ,filial, and control to use so:intCPFCNPJ = 000000000 and sintCPFCNPJFil = 0 and tintCPFCNPJCtrl = 90
. I found a gambiarra terrible, but I found it interesting to see other opinions, although the answer seems obvious.– Jothaz
Better use VARCHAR.
– epx