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(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
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.
- 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
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.
The varchar datatype, by contrast, consumes only the amount of actual space used plus 2 bytes for overhead