Does varchar size matter in temporary tables?

Question

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.

Answer 1

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:

1. Your user table column is varchar(50).
2. Your temp table column is varchar(255).
3. You have a record with 45 characters in that column in your user table.
4. 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

Answer 2

using varchar(255) for all varchar fields in temporary tables in stored procedures.

I would lean towards the use of the actual field length.

I read recently that MySQL (I'm assuming SQL Server is similar) temp tables allocate enough memory to store the maximum length possible for each varchar column... A systematic approach to allocating 200%-500% of the required memory for varchar fields in all stored procedures seems like an unnecessary draw on system resources. If you are ever using a significant amount of memory creating these temp tables then you could be unnecessarily claiming memory that was in use for caching, creating more work for the server at some point in the future, even after the store procedures are done.

Edit: See Bill Karwin's answer: https://stackoverflow.com/questions/1962310/importance-of-varchar-length-in-mysql-table