I just read that VARCHAR(MAX) (which can store, what, close to 2GB of char data?) is the recommended replacement for TEXT columns in 2005 and newer. My question is, if I want to search inside this column for any string ( ... WHERE COL1 LIKE '%search string%' ...) is it quicker to search a VARCHAR(MAX) using the LIKE clause, or use the TEXT column and put a Full Text Index (or whatever it's called) on this column, and then search using the CONTAINS clause?
Thanks..
For large text, the full text index is much faster. But you can full text index varchar(max) as well.
As far as I am aware and from what i remember from things i have had to do, i don't think you can search a text field without converting it. i might be wrong though, but you could always try it.
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.
++ on Joel's comment, plus remember that varchar(max) allocates bytes to the column as needed, where text has a fixed byte length.