SQL Server“文本"上的 WHERE 子句数据类型 [英] WHERE clause on SQL Server "Text" data type

问题描述

其中[CastleType]在SQL Server中设置为数据类型text"，查询为:

Where [CastleType] is set as data type "text" in SQL Server and the query is:

SELECT *
FROM   [Village]
WHERE  [CastleType] = 'foo' 

我收到错误:

数据类型TEXT 和VARCHAR 在等于运算符中不兼容.

The data types TEXT and VARCHAR are incompatible in the equal to operator.

我可以不使用 WHERE 子句查询此数据类型吗?

Can I not query this data type with a WHERE clause?

推荐答案

您可以使用 LIKE 代替 =.如果没有任何通配符，这将具有相同的效果.

You can use LIKE instead of =. Without any wildcards this will have the same effect.

DECLARE @Village TABLE
        (CastleType TEXT)

INSERT INTO @Village
VALUES
  (
    'foo'
  )

SELECT *
FROM   @Village
WHERE  [CastleType] LIKE 'foo' 

text 已弃用.更改为 varchar(max) 会更容易使用.

text is deprecated. Changing to varchar(max) will be easier to work with.

还有数据可能有多大?如果您要进行相等比较，则理想情况下您希望将此列编入索引.如果您将列声明为大于 900 字节的任何内容，则这是不可能的，尽管您可以添加可用于加速此类查询的计算的 checksum 或 hash 列

Also how large is the data likely to be? If you are going to be doing equality comparisons you will ideally want to index this column. This isn't possible if you declare the column as anything wider than 900 bytes though you can add a computed checksum or hash column that can be used to speed this type of query up.

这篇关于SQL Server“文本"上的 WHERE 子句数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！