One of the major reasons I do not allow text/image datatypes in my shop. SQL 2000 can not compart text to text. Probably because you would need up to 4GB of memory to do the comparison. See the artice "Managing ntext, text, and image Data" in BOL.
Originally Posted by BOL
When the ntext, text, and image data values get larger, however, they must be handled on a block-by-block basis. Both Transact-SQL and the database APIs contain functions that allow applications to work with ntext, text, and image data block by block.
Thats very likely, and very common. Also, the "text" datatype in MS Access is equivalent to the "varchar" datatype in SQL Server, so a lot of noob developers use the text datatype when upscaling from Access to SQL Server.
If it's not practically useful, then it's practically useless.