If I know that a table column only really needs to be varchar(100) i.e. data will not be longer than 100 characters, if I set the column to be varchar(256) will it make any difference?
From what I understand, since the column allows for variable length, having it at either 100 or 256 won't make any different so long as the data is never larger than 100.
Is this correct?
A varchar will only use as much space as inputted as opposed to a char which will pad with white space. Traffic size on a varchar column, therefore, is considerably smaller than a char column.
I'd set it to the business rule of 100 characters, the size is a constraint and will preserve the integrity of the data. You are just a rogue script or application bug away from breaking the 100 character limit and then possibly having invalid 100+ characters stored in the field.