The disadvantage of choosing a large MAX value for varchar or varbinary

Question

What is the disadvantage of choosing a large max value when creating a varchar or varbinary column?

I use MS SQL, but I assume this will be relevant to other dbs as well.

thanks

Answer 1

It depends on how reasonable it is to store a large amount of data in a particular column.

If you declare a column that will never store a lot of data (i.e. employee name as VARCHAR (1000)), you will get many problems

• Many, if not most client APIs (such as ODBC drivers, JDBC drivers, etc.) allocate memory buffers on the client that are large enough to hold the maximum size of a particular column. Therefore, even if the database should only store actual data, you can significantly increase the amount of memory used by the client application.
• You lose the ability to manage data validation rules (or transmit data information) from a table definition. If the database allows 1000 character names, each application that interacts with the database is likely to have its own rules for how large the employee name will be. If this is not mitigated by putting the stored procedure level between all applications and tables, this usually results in different applications having different rules.
• Murphy’s law states that if you make 1000 characters, someone will end up storing 1000 characters in a column, or at least a value large enough to cause errors in one or more applications (i.e. no one will check if each application employee name field can display 1000 characters).

Answer 2

Depends on the DBMS. IIRC, MySql allocates 2 bytes of overhead for varchars> 255 characters (for tracking varchar lengths). MSSQL <= 2000 will allow you to allocate a row size> 8060 bytes, but it fails if you try to insert an INSERT or UPDATE string that actually exceeded 8060 bytes. SQL 2005 [1] allows you to insert, but will select a new page for overflow and leave the pointer behind. This obviously affects performance.

[1] varchar (max) is a special case, but will also highlight an overflow page if the field length is> 8000 or a string> 8060. This is with the default settings of MSSQL, and the behavior can change with larger types in the data row option.

Answer 3

You can add the risk of hacking the application if some big data is somehow (for example, from the external interface), and your application is not designed to process it.

As a good design, you should always limit the margin size to a realistic value.