Using varchar(MAX) vs TEXT on SQL Server

by

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?

  1. Using a the LIKE clause against a VARCHAR(MAX) column?

    WHERE COL1 LIKE '%search string%'

  2. 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')

5 Answers
5

Leave a Comment