Best practices for SQL varchar column length [closed]

by

No DBMS I know of has any “optimization” that will make a VARCHAR with a 2^n length perform better than one with a max length that is not a power of 2.

I think early SQL Server versions actually treated a VARCHAR with length 255 differently than one with a higher maximum length. I don’t know if this is still the case.

For almost all DBMS, the actual storage that is required is only determined by the number of characters you put into it, not the max length you define. So from a storage point of view (and most probably a performance one as well), it does not make any difference whether you declare a column as VARCHAR(100) or VARCHAR(500).

You should see the max length provided for a VARCHAR column as a kind of constraint (or business rule) rather than a technical/physical thing.

For PostgreSQL the best setup is to use text without a length restriction and a CHECK CONSTRAINT that limits the number of characters to whatever your business requires.

If that requirement changes, altering the check constraint is much faster than altering the table (because the table does not need to be re-written)

The same can be applied for Oracle and others – in Oracle it would be VARCHAR(4000) instead of text though.

I don’t know if there is a physical storage difference between VARCHAR(max) and e.g. VARCHAR(500) in SQL Server. But apparently there is a performance impact when using varchar(max) as compared to varchar(8000).

See this link (posted by Erwin Brandstetter as a comment)

Edit 2013-09-22

Regarding bigown’s comment:

In Postgres versions before 9.2 (which was not available when I wrote the initial answer) a change to the column definition did rewrite the whole table, see e.g. here. Since 9.2 this is no longer the case and a quick test confirmed that increasing the column size for a table with 1.2 million rows indeed only took 0.5 seconds.

For Oracle this seems to be true as well, judging by the time it takes to alter a big table’s varchar column. But I could not find any reference for that.

For MySQL the manual says “In most cases, ALTER TABLE makes a temporary copy of the original table“. And my own tests confirm that: running an ALTER TABLE on a table with 1.2 million rows (the same as in my test with Postgres) to increase the size of a column took 1.5 minutes. In MySQL however you can not use the “workaround” to use a check constraint to limit the number of characters in a column.

For SQL Server I could not find a clear statement on this but the execution time to increase the size of a varchar column (again the 1.2 million rows table from above) indicates that no rewrite takes place.

Edit 2017-01-24

Seems I was (at least partially) wrong about SQL Server. See this answer from Aaron Bertrand that shows that the declared length of a nvarchar or varchar columns makes a huge difference for the performance.

More Related Contents:

  1. SQL: Repeat a result row multiple times, and number the rows
  2. Why do results from a SQL query not come back in the order I expect?
  3. how to cast the hexadecimal to varchar(datetime)?
  4. SQL Query needed for fetch value from Two identical table but not fully identical table
  5. sql query to modify the selected value from database [closed]
  6. Calculate Average of the largest values in multiple columns
  7. SQL Print the name of all Employees together with the name of their supervisor
  8. How to filter SQL results in a has-many-through relation
  9. How to select the nth row in a SQL database table?
  10. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  11. Storing JSON in database vs. having a new column for each key
  12. Simple Random Samples from a Sql database
  13. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  14. When to use SELECT … FOR UPDATE?
  15. Relationship between catalog, schema, user, and database instance
  16. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  17. What resources exist for Database performance-tuning? [closed]
  18. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  19. Are SELECT type queries the only type that can be nested?
  20. Postgres LEFT JOIN with WHERE condition
  21. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  22. What are the max number of allowable parameters per database provider type?
  23. What happens when I exhaust a bigint generated key? How to handle it?
  24. Why in SQL NULL can’t match with NULL?
  25. Convert SQL Server query to MySQL [duplicate]
  26. comparison of truncate vs delete in mysql/sqlserver [duplicate]
  27. Using column alias in WHERE clause of MySQL query produces an error
  28. Using an Alias in SQL Calculations
  29. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  30. SQL query to check if a name begins and ends with a vowel

Leave a Comment