Should I limit the size of VARCHAR columns for performance?

Asked

Viewed 1,481 times

6

Normally the maximum size for VARCHAR is 255.

If I create the column with a smaller size, say VARCHAR(50), I will gain performance or save disk space?

sql database

  • Which backend? Access? Mysql? Postgresql? SQL Server? Oracle?

  • Actually I’d like to know in each of these.

1 answer

7


varchar is dynamically allocated, if I have a var char of 30 positions and write a value with only 10 it will only occupy 10 positions.

This is valid for most of the market sgbds (MSSQL, Oracle, Mysql, Postgree, Firabird, Mariadb...).

Oracle also has varchar2, which supports a much larger amount of characters, but works the same way as varchar.

That said, limiting the size will not bring performance gains but will certainly optimize the space occupied by the table.

EDIT

If I have two varchar fields, one of 255 and one of 15. Filling them with 15 characters at the end of the accounts will have the same result in terms of allocated space, since sgbd will allocate only 15 characters for each one.

There are some reasons for threshold the size of varchar, the most important are:

  • Prevent the bank from growing uncontrolled, ex:

It doesn’t make sense for me to have a 255 varchar if I want to keep a phone number. This way the bank will complain if the user can include some random value.

  • More precise growth indicators

Sometimes you design the database and use tools that estimate database growth over time. This is important for large companies and public agencies that rely on bidding for the acquisition of new computational resources.

Not limiting the size of each varchar can make my indicator less accurate.

  • Make the template clear to the developer

It is interesting that the developer knows how much each field supports so that he also limits the amount of characters typed in each field.

Sources

varchar is defined in sql ansi (available here), besides being addressed in the official documentation of your DBMS. So what I said is there. It doesn’t make much sense to post here because it would be too long.

EDIT 2

The link I posted is only about the default changes in 2003 and may not have exactly what you’re looking for. These patterns are often paid for, so I couldn’t locate them for you... :(

  • So VARCHAR(15) will make the bank occupy less space than VARCHAR(255), even if I only store 15 characters every time? You have a source for that statement?

  • See if my Dit helps

  • 1

    @user26282 varchar has dynamic allocation. It will only save what you need (obvious, within the limit). Varchar is not indicated if the field is changed many times as it will have to allocate and relocate the space several times, for these cases it is indicated or when the size is fixed, char is indicated. Remembering that there is the nvarchar, it is worth checking if your application may one day need it.

  • @Italopessoa You can edit the answer with this information if you find it interesting.

  • edited by @Edgar-Muniz-Berlinck :)

Browser other questions tagged sql database

You are not signed in. Login or sign up in order to post.