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.

  • I'd argue that maybe you don't need temporary tables in the first place. What are they being used for? If they're needed, what are these specific columns being used for? Are they used in joins or comparisons of any kind? Are any of the underlying columns nvarchar and not varchar? Nov 16 '12 at 19:35
  • @AaronBertrand The temporary tables are there for modularity. Data gets transformed and populated a number of times based on business rules which may change. I think there are also a few MAX() in the mix. Nov 19 '12 at 18:25

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


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

  • 2
    Assuming SQL Server is similar? I wouldn't...
    – A-K
    Nov 20 '12 at 20:44
  • Sorry, I realise that my answer is incomplete. What I mean is that I would err on the side of caution (i.e. not making a change which could negatively effect performance) unless that assumption is established to be wrong.
    – Matt
    Nov 21 '12 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.