Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do.

I consistently see agreement that there's a performance hit if the entire row exceeds 8060 bytes. Other than that, I see disagreement.

Is the claim true that The default is SET ANSI PADDING ON = potential for lots of trailing spaces? As long as the total row width is less than 8060, are there any real performance concerns in over-sizing VARCHAR columns?

Evidence that column width matters


The same goes for CHAR and VARCHAR data types. Don’t specify more characters in character columns that you need.

http://www.sql-server-performance.com/2007/datatypes/


  • Length is a constraint on the data (like CHECK, FK, NULL etc)
  • Performance when the row exceeds 8060 bytes
  • Can not have unique constraint or index (key column width must be < 900)
  • The default is SET ANSI PADDING ON = potential for lots of trailing spaces

http://dba.stackexchange.com/questions/3160/what-are-the-consequences-of-setting-varchar8000


Evidence that column width DOES NOT matter


If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.

overstating field size in database design


The varchar datatype, by contrast, consumes only the amount of actual space used plus 2 bytes for overhead

http://sqlfool.com/content/PerformanceConsiderationsOfDataTypes.pdf


share|improve this question
2  
    
900 bytes is also a "magical" limit, since anything larger than 900 bytes cannot be indexed –  marc_s Jan 25 '12 at 20:36
    
@MartinSmith: That's good information about estimated memory requirements. –  Eric J. Jan 25 '12 at 21:42
    
@marc_s: Can the entire column not be indexed, or will an index on the column only consider the first 900 bytes? I know MySQL allows indices on the first N-bytes of a column but not sure how SQL Server works. –  Eric J. Jan 25 '12 at 21:43
    
@EricJ.: if the column can exceed 900 bytes (e.g. is defined as varchar(1000)), it cannot be indexed (it can be used as an included column in a non-clustered index - but that's a rather obscure edge case). This also applies to varchar(max) columns - which should be used only when absolutely necessary –  marc_s Jan 25 '12 at 21:53
add comment

migrated from stackoverflow.com Jan 26 '12 at 2:48

This question came from our site for professional and enthusiast programmers.

2 Answers

AS you indicated, as long as the row size does not exceed 8060 (or whatever the maximum is set to), there is no performance difference in using VARCHAR(30) or VARCHAR(255) or larger. The CHAR to VARCHAR comparison from the linked article is not really pertinent. Although I agree you should not specify more space than you are sure you need, in many cases you do not know just how much space you will actually need. So be liberal with VARCHAR space - I'm pretty sure increasing the size of fields requires a table rebuild, while decreasing is a simple ALTER TABLE statement.

share|improve this answer
5  
Increasing the size of variable length columns is a simple metadata change (except for increasing to max from non max). It is the opposite direction that has the higher overhead. –  Martin Smith Jan 25 '12 at 20:18
add comment

The question might be better stated as:

"What is the advantage of over-specifying the maximum length of a variable-length column?"

In general, there is little advantage, and several disadvantages as the various linked answers point out. Aside from any other concerns, consider that SQL Server is not open-source: there are many 'magic numbers' and heuristics applied based on the information provided to the system. Without source code access, we could never be entirely sure what the impact of this practice might be.

In some cases, where the average length of a column is significantly higher than the 50% assumed by SQL Server when calculating sort/hash memory grants, you may see a performance improvement by over-specifying the maximum length. This is a dubious workaround, and should probably only be applied by an explicit CAST or CONVERT (with comments!) rather than changing the base column definition. Sometimes, it will be preferable to rewrite the query to sort keys instead of whole rows anyway.

Where the maximum row size might exceed the in-row limit (even if no rows actually do), deleting rows can cause unexpected page-splitting if a trigger is present. Updates may also lead to fragmentation via the same mechanism.

SQL Server does a pretty good job in most cases where it is provided with good, accurate information from metadata. Compromising this principle for 'convenience' seems unwise to me. A sensible approach is to choose a maximum length value that is reasonable according to the actual data to be stored, and any foreseeable changes.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.