Using varchar MAX vs TEXT on SQL Server

Question

I just read that the VARCHAR MAX  datatype  which can store close to 2GB of char data  is the recommended replacement for the TEXT datatype in SQL Server 2005 and Next SQL SERVER versions    If I want to search inside a column for any string  which operation is quicker    Using a the LIKE clause against a VARCHAR MAX  column   WHERE COL1 LIKE   search string   Using the TEXT column and put a Full Text Index Catalog on this column  and then search using the CONTAINS clause   WHERE CONTAINS  Col1   MyToken

User · Answer

If using MS Access  especially older versions like 2003  you are forced to use TEXT datatype on SQL Server as MS Access does not recognize nvarchar MAX  as a Memo field in Access  whereas TEXT is recognized as a Memo-field

User · Answer

Basic Definition   TEXT and VarChar MAX  are Non-Unicode large Variable Length character data type  which can store maximum of 2147483647 Non-Unicode characters  i e  maximum storage capacity is  2GB     Which one to Use     As per MSDN link Microsoft is suggesting to avoid using the Text datatype and it will be removed in a future versions of Sql Server  Varchar Max  is the suggested data type for storing the large string values instead of Text data type    In-Row or Out-of-Row Storage    Data of a Text type column is stored out-of-row in a separate LOB data pages  The row in the table data page will only have a 16 byte pointer to the LOB data page where the actual data is present  While Data of a Varchar max  type column is stored in-row if it is less than or equal to 8000 byte  If Varchar max  column value is crossing the 8000 bytes then the Varchar max  column value is stored in a separate LOB data pages and row will only have a 16 byte pointer to the LOB data page where the actual data is present  So In-Row Varchar Max  is good for searches and retrieval    Supported Unsupported Functionalities    Some of the string functions  operators or the constructs which doesn   t work on the Text type column  but they do work on VarChar Max  type column      Equal to Operator on VarChar Max  type column Group by clause on VarChar Max  type column   System IO Considerations    As we know that the VarChar Max  type column values are stored out-of-row only if the length of the value to be stored in it is greater than 8000 bytes or there is not enough space in the row  otherwise it will store it in-row  So if most of the values stored in the VarChar Max  column are large and stored out-of-row  the data retrieval behavior will almost similar to the one that of the Text type column   But if most of the values stored in VarChar Max  type columns are small enough to store in-row  Then retrieval of the data where LOB columns are not included requires the more number of data pages to read as the LOB column value is stored in-row in the same data page where the non-LOB column values are stored  But if the select query includes LOB column then it requires less number of pages to read for the data retrieval compared to the Text type columns   Conclusion  Use VarChar MAX  data type rather than TEXT for good performance   Source

User · Answer

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

User · Answer

You can t search a text field without converting it from text to varchar   declare  table table  a text  insert into  table values   a   insert into  table values   a   insert into  table values   b   insert into  table values   c   insert into  table values   d     select   from  table where a   a    This give an error   The data types text and varchar are incompatible in the equal to operator    Wheras this does not   declare  table table  a varchar max     Interestingly  LIKE still works  i e   where a like   a

User · Answer

For large text  the full text index is much faster   But you can full text index varchar max as well

[sql-server] Using varchar(MAX) vs TEXT on SQL Server

Examples related to sql-server

Examples related to performance

Examples related to text

Examples related to varchar

Examples related to sql-types