SQL Server Performance

Between Operator usage with char(1)

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Dec 9, 2009.

  1. atulgoswami New Member

    Can we use Between operator with Char(1) datatype? Normally, i have used with numeric data only.
    Now, it is working but i think this approach is creating some performance problem. I am not sure whether between operator works with char(1) as efficiently as it works with numerics.
    Any idea/suggestion would be of great help.
    Thanks
    atulgoswami, Dec 9, 2009
    #2

  2. Adriaan New Member

    You can use the BETWEEN operator on any data type by which you can order a rowset. (Note that the ordering may not be quite as predictable as you would like it.)
    Comparing a character string, regardless of length, will be less efficient than comparing a numeric value, even if the character is a number.
    Adriaan, Dec 9, 2009
    #3

  3. FrankKalis Moderator

    [quote user="Adriaan"]
    You can use the BETWEEN operator on any data type by which you can order a rowset. (Note that the ordering may not be quite as predictable as you would like it.)
    Comparing a character string, regardless of length, will be less efficient than comparing a numeric value, even if the character is a number.
    [/quote]
    Hm, for longer strings I would agree, but a char(1) & say a tinyint have the same storage requirements. Why should a comparison of string then be less efficient? Just wondering though...
    FrankKalis, Dec 9, 2009
    #4

  4. Adriaan New Member

    Perhaps I should have said "potentially less performant" instead of "less efficient". As long as one stays in the 0-9 and a-z ranges, the difference is probably negligible.
    CHAR(1) is an extreme case, and will be different in this respect from even CHAR(2), let alone longer string data types. Not to mention case and/or accent sensitive collations ...
    Adriaan, Dec 9, 2009
    #5

  5. FrankKalis Moderator

    Just guessing, but a char(1) consumes always the same fixed amount of space and even less than most numeric data types. If you have the choice between a char(1) and a tinyint there is no advantage of preferring the numeric tinyint apart from the fact that a tinyint offers you more possible values. So, I don't think you will run into any performance issue here.
    FrankKalis, Dec 9, 2009
    #6

Share This Page