Is there any way to DISTINCT or group by a text (or ntext) in SQL Server 2005?
sql, sql-server, types
Solution
One hack around it is to cast it as an `nvarchar(max)`.
This is a documented way to increase the string length beyond 4,000:
`nvarchar [ ( n | max ) ]`
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.
A similar trick applies to varchar().
Problem
In a table, I have a column called MEMO_TEXT that is a text data type. When I try creating a view and use a GROUP BY, I get the following error: SQL Server Database Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. I get this error when I try to do a DISTINCT on the query as well. Any ideas on how to get around this? If you need any more information, please let me know.