This was a great question. Well done, John! I hope you have more of these in store for us.
diamondgm (10/16/2009)
Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).
That's not an assumption, the even spread is explicitly mentioned in the question. Without it, I agree that the question would have been harder (if not impossible) to answer.
Secondly, within SQL Server, the efficiency may be best to use char in that instance, but take in to account the code operations of trimming and I think it becomes debatable.
I doubt it. Almost all business applications I have seen have their performance tied to an I/O bottleneck, and have the CPU twiddling it's virtual thumbs while waiting for more data to be read or written. Those few cycles the CPU spends to trim trailing spaces won't affect performance at all, it just means one less thumb to twiddle.
Oh, and if you really want to compare CPU times, then don't forget that SQL Server has to do extra work for each varying length column (like getting the length, then the data; or comparing length and adding trailing spaces before each comparison). So you'll probably find that the fixed length has lower CPU usage in spite of the extra trimming operations. Not that it will affect overall performance, though.
I agree with you on a purist level, but practically, I would go with varchar still simply for the programmers' ease.
How, exactly, is varchar easier for a programmer?
hakan.winther (10/16/2009)
personally, I would only use char in rare cases unless all the data for the column are equal.
That's reallly a shame, as the only good reasons to use varchar instead of char are a difference of more than 2 between average and maximum length; or a need to preserve trailing spaces.