Use VARCHAR(255) for all fields or choose the best size for each field?

Question

Assuming the following fields (for example): Nome, Rua, Cidade ,UF.

The Nome would be the client’s name. There are people with 2 short names or people with 5 or more names, so it is possible to have a "João Silva" or a "Maria Clara Ramos Santos da Silva", that is, people with 10 characters and people with 27 characters (or even more).

The Rua would be the customer’s street name: Same case as the previous example.

The Cidade would be the city of the customer: Same case as the previous example.

The UF would be the client’s state: As I would like only the Acronym, it will not have been with 5 or more letters, only 2.

It is worth putting VARCHAR(255) for everything or is it better to choose which size is best for each field? When I say "best," I am referring to nay spend unnecessary space, like having a 255 character field and only storing values with only 2 letters (UF). I read in other answers than the VARCHAR() is a dynamic format, the value passed between parentheses is just the maximum value that field will support, is that true? If it is, then it means it’s worth putting VARCHAR(255) for all fields, since the value will be dynamic with a maximum of 255, so the maximum is 255, but it will only have 2 letters so it will store only the space needed for 2 letters and not 255.

"Keep picking better size" = Nome VARCHAR(100), Rua VARCHAR(120), Cidade VARCHAR(100), UF VARCHAR(2).

I had this doubt because I am about to create a database for a Mysql system, but my doubt refers to the database in general, not only Mysql.

Answer 1

Contrary to what you’re imagining VARCHAR does not mean that it will have 255 characters. It will occupy essentially the necessary amount. It will not be the same amount of bytes because the encoding can have characters with more than one byte and has a few bytes of control.

The CHAR occupies the size you set. And has no additional cost of control. The state would be CHAR(2). I prefer it whenever the data has known size.

I use the VARCHAR with a number of as many characters as that column can have. Although I assure in the application that the maximum size will always be respected, it is good to have a trigger if it escapes some programming error. A name I would use 60 or something, not because it costs more to put 255, just because I see no advantage in it and has at least one disadvantage. So valid is, good is not.

Some databases may be slightly different, but more or less the same.

If you’re too lazy to think of the most suitable number then my advice is to find another profession :)

If it’s in other responses and they have multiple votes, it’s from an experienced user, it’s almost 100% sure it’s true.

Answer 2

This answer is specific to SQL Server.

In the SQL Server documentation for the data type sweep(n) "Storage size is the actual size of the entered data + 2 bytes". That is, if in a given row the size of the string to be stored in the column is 12 bytes, regardless of whether the column is declared as varchar(20) or varchar(8000) in the database that information will occupy 14 bytes. Assuming a column with a maximum content of 60 characters, then either varchar(60) or varchar(255).

However, when the SQL Server query optimizer generates the query execution plan, and certain actions occur, it is necessary to estimate how much memory should be allocated in advance for the query execution - Grant memory -, and one of the parameters for this calculation is the line size. In the case of rows where there are columns of variable size, the manager does not keep information about what is the average size of the information recorded in the column and then uses arbitrary value, set from the maximum size that the column allows. For example, in the case of a column declared as varchar(255), the query optimizer considers half plus 2 bytes, 128 + 2, as the value to be used in the calculation of the estimated row size in memory. That is, it will allocate 130 bytes even if the column size can be up to 60 characters. Here it is clear that more memory will be allocated than necessary.

So in the case of SQL Server always declare the correct size; do not overestimate.

Beyond the Grant memory there are other situations in which overestimating the size of columns of variable size brings disadvantages. In the article How to set the maximum size of columns of variable size? there is detailed explanation of the negative impact on overestimating the size of columns of variable size, including images of execution plans.

Answer 3

For fields where you have an obvious notion of how many characters you will receive, it is good to use the CHAR(numero de caracteres), but before, check the type of character that will be received, if it is of the whole type, text, date, among others. Also, it is good to pay attention to these details and so you will save bytes on your DBMS and your Databases (Schemas).

remarks:

• CHAR occupies all character number chosen;
• VARCHAR occupies only the entered characters, eliminating the amount that becomes vacant, but occupies some bytes due this amount becomes vacant.