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.
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