在SQL Server上使用varchar(MAX)vs TEXT [英] Using varchar(MAX) vs TEXT on SQL Server

问题描述

我刚刚读到，在SQL Server 2005和Next SQL SERVER版本中，建议将VARCHAR(MAX)数据类型(可以存储接近2GB的char数据)替换为TEXT数据类型.

如果我想在列内搜索任何字符串，哪个操作更快?

1. 对VARCHAR(MAX)列使用LIKE子句吗?

   WHERE COL1 LIKE '%search string%'

2. 使用TEXT列并在该列上放置全文索引/目录，然后使用CONTAINS子句进行搜索?

   WHERE CONTAINS (Col1, 'MyToken')

解决方案

VARCHAR(MAX)类型是TEXT的替代.基本区别是TEXT类型将始终将数据存储在blob中，而VARCHAR(MAX)类型将尝试将数据直接存储在行中，除非它超过8k限制，然后将其存储在blob中

在两个数据类型之间使用LIKE语句是相同的. VARCHAR(MAX)给您的附加功能是它还可以与=和GROUP BY一起使用，就像其他任何VARCHAR列一样.但是，如果您确实有很多数据，那么使用这些方法会遇到巨大的性能问题.

关于是否应使用LIKE进行搜索，还是应使用全文索引和CONTAINS.不论VARCHAR(MAX)还是TEXT，这个问题都是相同的.

如果要搜索大量文本，而性能是关键，则应使用全文索引.

LIKE易于实现，通常适用于少量数据，但由于无法使用索引，因此在处理大数据时性能极差.

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

解决方案

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.

这篇关于在SQL Server上使用varchar(MAX)vs TEXT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！