Menu
Below is an sql statement and an error I'm receiving. I want to group all returned items by the prodID.
Error:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Statement:
SELECT TOP 20
PRODID, ITEMDES
FROM orderedItems oi
left join orders o on oi.order_id = o.order_id
Group by PRODID, ITEMDES
985
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
NTEXT is a variable-length data type that can store long Unicode character strings. NTEXT can hold up to 2,147,483,647 bytes of data. The actual storage used depends on the length of the character string. Note: NTEXT has been deprecated and will be removed in some future release of SQL Server.
ntext will always store its data in a separate database page, while nvarchar(max) will try to store the data within the database record itself. So nvarchar(max) is somewhat faster (if you have text that is smaller as 8 kB). I also noticed that the database size will grow slightly slower, this is also good.
Text - Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters. nText - Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. You can get lot of info about this in SqlServer Books online...
No, they can't. Additionally, they're deprecated in favour of (n)varchar(max) types.
If you need to group them, either change your data structure from (n)text to (n)varchar(max) or do a convert in your group clause
GROUP BY ProdID, CONVERT(nvarchar(max), ItemDes)
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
