Using varchar(MAX) vs TEXT on SQL Server

by

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.

More Related Contents:

  1. SQL Server Text type vs. varchar data type [closed]
  2. What makes a SQL statement sargable?
  3. What is the difference between varchar and nvarchar?
  4. What do Clustered and Non-Clustered index actually mean?
  5. Which are more performant, CTE or temporary tables?
  6. What are the main performance differences between varchar and nvarchar SQL Server data types?
  7. What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
  8. SQL Server: Query fast, but slow from procedure
  9. XML Server XML performance optimization
  10. SQL Server pivot vs. multiple join
  11. LIKE vs CONTAINS on SQL Server
  12. SQL Server Query: Fast with Literal but Slow with Variable
  13. Does the order of columns in a WHERE clause matter?
  14. cross apply xml query performs exponentially worse as xml document grows
  15. nvarchar(max) vs NText
  16. Which is quicker COALESCE OR ISNULL?
  17. How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?
  18. How to convert text column to datetime in SQL
  19. Slow query with cfqueryparam searching on indexed column containing hashes
  20. How can foreign key constraints be temporarily disabled using T-SQL?
  21. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  22. SQL Server: Database stuck in “Restoring” state
  23. How to check if a Constraint exists in Sql server?
  24. Access is denied when attaching a database
  25. Getting an odd error, SQL Server query using `WITH` clause
  26. What does “ORDER BY (SELECT NULL)” mean?
  27. SQL Unique constraint across multiple tables
  28. Changing SQL Server collation to case insensitive from case sensitive?
  29. How to store directory / hierarchy / tree structure in the database?
  30. Merge multiple rows into a single row

Leave a Comment