The inability to use select distinct and search using LIKE seem like major limitations to the text/ntext fields.
This was a great article, but the solutions seem like some major pains in dealing with this type of field, i.e. having to write speacial queries and use tricks just to see the data at all!
Hopefully these problems are being addressed with Yukon/2005
Given expression Return type text varchar
SELECT CHARINDEX(@Delimiter, @String, @number)