hakan.winther (10/16/2009)
Hugo Kornelis (10/16/2009)
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.Of course, if i need the trailing spaces I would go for char
OOPS - misunderstanding alert!
If you need to preserve trailing spaces, you MUST use varchar. Varchar stores the data with the trailing spaces as they were on input (that is, with default settings). Char pads data with spaces to the specified length and there is no way to reconstruct which spaces were already there and which were added by SQL Server to get the string at the proper length.
but I have seen statements where the programmer doesn't want the trailing spaces and uses RTRIM to remove then and also uses RTRIM the in the where clause fetch the records regardless to the spaces:
SELECT somecol FROM sometable WHERE RTRIM(anothercol)='somevalue'
The real problem of these programmers (apart from not understanding how SQL Server indexes work) is a lack of understanding of string comparisons. The RTRIM is completely superfluous, both for char and varchar datatypes. Trailing spaces are compared in these comparisons anyway. (Well, technically the shorter string is padded with spaces to match the length of the longer string before starting a character-by-character comparison, but that has the exact same effect as ignoring trailing spaces).