Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
–
Aaron Bertrand♦Nov 16 '12 at 19:35

4

Why 255? why not 500 or even varchar(max). Where does this magic number com from?
–
a_horse_with_no_nameNov 17 '12 at 14:01

The defined size of a varchar column has no impact on the performance whatsoever. That is an urban legend that stems from the last millenium.
–
a_horse_with_no_nameNov 17 '12 at 14:48

@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.
–
Brian Nickel♦Nov 19 '12 at 18:25

2 Answers
2

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.

You shouldn't see implicit conversions joining, say, VARCHAR(50) = VARCHAR(255) - do you have a plan that demonstrates this? I'd expect it for VARCHAR(anything) = NVARCHAR(anything), but you won't see implicit conversion for two varchars of different lengths. At least not in modern versions of SQL Server.
–
Aaron Bertrand♦Nov 16 '12 at 22:06

@AaronBertrand You are absolutely correct. This was a misconception on my part. I have altered my answer to reflect this. Thanks!
–
Matt MNov 17 '12 at 0:01

In my wife's case, the temporary table is being used for a report and the results are not stored back in the database, so this shouldn't be an issue. Very good point that assumptions and constraints should be documented.
–
Brian Nickel♦Nov 19 '12 at 18:29

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.

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.
–
MattNov 21 '12 at 10:20