• 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, 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'

    And this causes the SQL Server to do a table or index scan (lots of IO) with terrible performance as a result. This issue is not solved completely by using varchar, but I can reduce some of the cases where the programmers are using functions on the left side of the equal sign. The correct way to solve this issue is to teach everyone to be careful of how they use functions in the where clause, and I do that all the time, but... new project, new programmers.

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform