WHERE clause on SQL Server "Text" data type

Question

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

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

I get the error:

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

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

Answer 1

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 is deprecated. Changing to varchar(max) will be easier to work with.

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.