one character of data

  • pankaj upadhyay (8/23/2010)

    thanks actually i get confused after reading the ans Either char(1) or varchar(1) may be used. for this question that when we take varchar(1) than will it save length or not.

    anyway thanks again.

    No, in this case varchar will not save space, it will cost more space.

    For varchar, the space taken is (actual length) + (2 bytes overhead). So for a one-char string, the actual space is 1 + 2 = 3 bytes; for an empty string the actual space is 0 + 2 = 2 bytes.

    For char, the space taken is (declared length). So both for a one-char string and the empty string, the actual space taken is always exactly 1 byte.

    Varying length strings should only be considered when the difference between average actual length and maximum length is more than 2. If it's less than two, always use fixed length. If it's way more than two, varying length is a no-brainer. Between 2 and, let's say, 3.5 - those are the cases when you really have to consider other stuff.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog:
    SQL Server Execution Plan Reference:

  • Hugo is correct, a varchar(1) or nvarchar(1), doesn't make sense. It uses more space. Not much, but more.

    I have typically gone with char(4) or less. When I get to 5, I usually just go varchar at this point, especially as many fields above this size aren't necessarily set. Too often I've seen someone say that the values above 5 are set, like for US phone numbers or zip codes, however in my career we've gone to 9 digit zip codes and 11-13 digit phones.

  • With char(n) where n > 1 you must take care of ANSI PADDING settings. It's another story.

    nchar is "better", it always behaves as set to ON.

    From this point of view varchar(2) can be more comfortable, but it takes some extra space.

    See, understand, learn, try, use efficient
    © Dr.Plch

Viewing 3 posts - 181 through 182 (of 182 total)

You must be logged in to reply to this topic. Login to reply