Does varchar size matter in temporary tables?

There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work even if the definition changes, and the other camp wants to stick with the size in the source tables for potential performance improvements.

Is the performance camp right? Are there other implications? They are using SQL Server.

Depending on how you are using your temp tables, you could run into a data truncation issue.

This example is a bit contrived, but it illustrates my point.
Example:

• Your user table column is varchar(50).

• Your temp table column is varchar(255).

• You have a record with 45 characters in that column in your user table.

• In your procedure, you concatenate ' - for the win' to the end of that column, prior to merging that temp table into your user table.

The temp table would gladly accept the new varchar value with a length of 59. However, your user table could not. Depending on how you handle this in your procedure, this could result in truncation or an error.

Unless you document and account for these issues, your procedure could perform in an unexpected manner.

Personally, I do not think there is an answer to this question that is correct 100% of the time. It really depends on how you are using those temp tables.

Hope this helps

StackExchange Database Administrators Q#28817, answer score: 6