Menu
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.
968
You can use: SELECT DISTINCT CONVERT(varchar(max), text_column) ... Or for less memory usage, if you're happy with the first x bytes (say, 900): SELECT DISTINCT LEFT(text_column, 900) ...
Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.
DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY.
Distinct is used to find unique/distinct records where as a group by is used to group a selected set of rows into summary rows by one or more columns or an expression. The functional difference is thus obvious. The group by can also be used to find distinct values as shown in below query.
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().
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
