none
Database - Char vs Varchar(1), Should I only use Varchar? RRS feed

  • Question

  • I am wondering if I should change the DataType to "varchar" across the whole database and get rid of "char" type.

    Here below are some examples:

    [1]
    Field: is_flagged 
    DataType: char <---- problem right here
    Value: Y or N

    [2]
    Field: status_code
    DataType: varchar(1) <---- problem right here
    Value: A, B or C

    [3]
    Field: type_code

    DataType: varchar(2)  <--- this looks great
    Value: XS, XM or XL 

    If you look at the fields of "is_flagged" and "status_code", they are pretty much the same.  Should I only use "Varchar" datatype?

    Please advice, and thanks !

    Friday, May 22, 2020 3:37 AM

All replies

  • Hi Ihandler MSDN,

    You can use both of them,however the big difference is as next:

    char: fixed size;

    vchar:variable siza.

    More information:data-types/char-and-varchar-transact-sql

    Best Regards.

    yuxi

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 22, 2020 6:46 AM
  • I am wondering if I should change the DataType to "varchar" across the whole database and get rid of "char" type.

    The short answer is: no, you should not do that.

    You use varchar for variable length data, so varchahr(1) only make sense if you want to distinguish between '' and ' '. And since trailing spaces are insignificant in SQL Server, this distinction is kind of hard to make.

    Also, char(1) takes up one byte. varchar(1) takes up two or three bytes. That is, there are two bytes to hold the length.

    Use char when the data is very short, up to three bytes, or fixed in length. For instance, shares and other financial instruments are identified by a 12 character ISIN code, and since this is always 12 characters, you would use char(12) twelve in this case.

    On the other hand, say that you have a name column which can be up to 30 characters long. Here char(30) would be a mistake, since most names are a ot shorter. With char(30) you would store a lot of insignificant space characeters.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 22, 2020 8:15 AM
  • Hi Ihandler MSDN,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 25, 2020 1:23 AM
  • Hi Ihandler MSDN,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 26, 2020 1:14 AM
  • Hi Ihandler MSDN,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 27, 2020 1:16 AM
  • Hi Ihandler MSDN,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 29, 2020 12:55 AM