SQL Server varchar(50) and varchar(128) performance difference [duplicate]

Question

Possible Duplicate:
is there an advantage to varchar(500) over varchar(8000)?

I am currently working on a table which has lots of columns with varchar(50). The data, we now have to insert in some columns is above 50 characters so we have to change the column size from 50 to 128 for those columns, since we have a lot of columns its a waste of time to change individual columns.

So I proposed to my team, why dont we change all the columns to varchar(128). Some of the team mates argued that this will cause a performance hit during select and join operations.

Now I am not an expert on databases but I dont think moving from varchar 50 to varchar 128 will cause any significant performance hit.

P.S - We dont have any name, surname, address kind of data in those columns.

Answer 1

varchar(50) and varchar(128) will behave pretty much identical from every point of view. The storage size is identical for values under 50 characters. They can be joined interchangeably (varchar(50) joined with varchar(128)) w/o type convertion issues (ie. an index on varchar(50) can seek a column varchar(128) in a join) and same applies to WHERE predicates. Prior to SQL Server 2012 ncreasing the size of a varchar column is a very fast metadata-only operation, after SQL Server 2012 this operation may be a slow size-of-data-update-each-record operation under certain conditions, similar to those descirbed in Adding a nullable column can update the entire table.

Some issues can arrise from any column length change:

• application issues from handling unexpected size values. Native ones may run into buffer size issues if improperly codded (ie. larger size can cause buffer overflow). Managed apps are unlikely to have serious issues, but minor issues like values not fitting on column widths on screen or on reports may occur.
• T-SQL errors from truncating values on insert or update
• T-SQL silent truncation occuring and resulting in incorrect values (Eg. @variables declared as varchar(50) in stored proc)
• Limits like max row size or max index size may be reached. Eg. you have today a composite index on 8 columns of type varchar(50), extending to varchar(128) will exceed the max index size of 900 and trigger warnings.

Martin's warning about memory grants incresing is a very valid concern. I would just buy more RAM if that would indeed turn out to be an issue.