locked
sql server 2008 data types RRS feed

  • Question

  • In a sql server 2008 r2 database, I am trying to determine which of the following two data types is better to use and why. The data types are:

    1. varchar(max) or

    2.text.

    The field in question is a comment field and the size of the comment change change.  Thus can you tell me what is the beter datatype and size to use?

    Also is a 'text' field variable length or will the size of the field always remain around 8, 000 bytes?

    Monday, August 6, 2012 1:55 PM

Answers

  • The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

    Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHAR column can be. However, if you do have a lot of data you will have a huge performance issue using these methods.

    In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.

    If you are searching large amounts of text and performance is key then you should use a Full Text Index.

    LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.

    So the answer is better to choose VARCHAR(MAX).


    Many Thanks & Best Regards, Hua Min


    Monday, August 6, 2012 2:05 PM

All replies

  • Use varchar(max); the text datatype is being slowly phased out.  In addition, the varchar(max) datatype is in general easier to work with.
    • Edited by Kent Waldrop Monday, August 6, 2012 1:59 PM
    • Proposed as answer by Naomi N Monday, August 6, 2012 2:19 PM
    Monday, August 6, 2012 1:57 PM
  • The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

    Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHAR column can be. However, if you do have a lot of data you will have a huge performance issue using these methods.

    In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.

    If you are searching large amounts of text and performance is key then you should use a Full Text Index.

    LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.

    So the answer is better to choose VARCHAR(MAX).


    Many Thanks & Best Regards, Hua Min


    Monday, August 6, 2012 2:05 PM