在SQL Server上使用varchar（MAX）vs TEXT

5 回答

• 287

  对于大文本，全文索引要快得多 . 但你也可以全文索引 varchar(max) .

  回复于 2024-04-26T12:03:45+08:00
• 8

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

  在两种数据类型之间使用LIKE语句是相同的 . VARCHAR(MAX) 的附加功能是，它也可以与 = 和 GROUP BY 一起使用，因为任何其他 VARCHAR 列都可以 . 但是，如果您确实拥有大量数据，则使用这些方法会产生巨大的性能问题 .

  关于是否应使用 LIKE 进行搜索，或者是否应使用全文索引和 CONTAINS . 无论 VARCHAR(MAX) 或 TEXT 如何，这个问题都是一样的 .

  如果您正在搜索大量文本并且性能很关键，那么您应该使用全文索引 .

  LIKE 更易于实现，并且通常适用于少量数据，但由于无法使用索引，因此大数据的性能极差 .

  回复于 2024-04-26T12:03:45+08:00
• 17

  如果不将文本字段从文本转换为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'
  

  这给出了一个错误：

  The data types text and varchar are incompatible in the equal to operator.
  

  这不是：

  declare @table table (a varchar(max))
  

  有趣的是， LIKE 仍然有效，即

  where a like '%a%'
  

  回复于 2024-04-26T12:03:45+08:00
• 4

  • Basic Definition

  TEXT 和 VarChar(MAX) 是非Unicode大型可变长度字符数据类型，最多可存储2147483647个非Unicode字符（即最大存储容量为：2GB） .

  • Which one to Use?

  根据MSDN link，Microsoft建议避免使用Text数据类型，它将在未来版本的Sql Server中删除 . Varchar（Max）是用于存储大字符串值而不是Text数据类型的建议数据类型 .

  • In-Row or Out-of-Row Storage

  Text 类型列的数据存储在单独的LOB数据页中的行外 . 表数据页中的行只有一个16字节指针指向存在实际数据的LOB数据页 . 而 Varchar(max) 类型列的数据如果小于或等于8000字节则存储在行内 . 如果Varchar（max）列值超过8000字节，则Varchar（max）列值存储在单独的LOB数据页中，并且行将只有一个16字节指针指向存在实际数据的LOB数据页 . 所以 In-Row Varchar（Max）适合搜索和检索 .

  • Supported/Unsupported Functionalities

  一些字符串函数，运算符或在Text类型列上不起作用的构造，但它们可以在VarChar（Max）类型列上工作 .

  • = 等于VarChar（Max）类型列上的运算符

  • 关于VarChar（Max）类型列的分组子句

  • System IO Considerations

  我们知道只有当存储在其中的值的长度大于8000字节或者行中没有足够的空间时，VarChar（Max）类型的列值才会存储在行外，否则它将存储它在行中 . 因此，如果存储在VarChar（Max）列中的大多数值都很大并且存储在行外，则数据检索行为几乎与Text type列的行为相似 .

  但是，如果存储在VarChar（Max）类型列中的大多数值都足够小，可以存储在行中 . 然后，检索不包括LOB列的数据需要读取更多数据页，因为LOB列值存储在存储非LOB列值的同一数据页中的行内 . 但是，如果select查询包含LOB列，那么与Text类型列相比，它需要更少的页面来读取数据 .

  Conclusion

  使用 VarChar(MAX) 数据类型而不是 TEXT 以获得良好性能 .

  Source

  回复于 2024-04-26T12:03:45+08:00
• 15

  如果使用MS Access（特别是像2003这样的旧版本），则必须在SQL Server上使用 TEXT 数据类型，因为MS Access无法将 nvarchar(MAX) 识别为Access中的备注字段，而 TEXT 则被识别为备注字段 .

  回复于 2024-04-26T12:03:45+08:00