How long does an nvarchar field need to be before it is better to use a text field in SQL Server? What are the general indications for using one or the other for textual content that may or may not be queried?
From what I understand, the TEXT datatype should never be used in SQL 2005+. You should start using VARCHAR(MAX) instead.
See this question about VARCHAR(MAX) vs. TEXT.
UPDATE (per comment):
This blog does a good job at explaining the advantages. Taken from it:
But the pain from using the type text comes in when trying to query against it. For example grouping by a text type is not possible.
Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file).
So basically, VARCHAR(MAX) keeps the data with the record, and gives you the ability to treat it like other VARCHAR types, like using GROUP BY and string functions (LEN, CHARINDEX, etc.).
For TEXT, you almost always have to convert it to VARCHAR to use functions against it.
But back to the root of your question regarding efficiency, I don't think it's ever more efficient to use TEXT vs. VARCHAR(MAX). Looking at this MSDN article (search for "data types"), TEXT is deprecated, and should be replaced with VARCHAR(MAX).