Index Strategies on Text or NVARCHAR(MAX) Fields

Question

I have the following query (simplified for question) I'm trying to speed up for a read only DB ...

SELECT 
 [sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)   
,[From]=[from_addr]   
,[To]=[to_addr]  --I'm a very long Text or NVARCHAR(MAX) Field
,[Subject]=[subject]  
,CASE WHEN [attach] = 1 THEN 'Yes' ELSE 'No' END AS 'Att'   
,[Code]=[ccode]   
,[Staff]=[staff]  
,[MatNo]=[mat_no]  
FROM dbo.[email] 
DYNAMIC WHERE CLAUSE ON ANY OF ABOVE

I've tried adding some indexes including covering indexes I can't include the to_addr the way it is (as text or NVARCHAR(MAX) col), and the query optimizer ends up using the clustered index because the to_addr field is not included. What are some ways to handle a situation like this ? Unfortunately I' limited to 2005 on this.

Edit

Tried adding Full_Text For to_addr still does a table scan. However if I comment out that line out it will use the index. : ( Damn you Text Data !

Answer 1

Why do you think anything but a scan should be used to pull back all the data? A full-text index won't really help - that helps you search those columns, but if you're just returning all the data (for any variety of WHERE clauses) then there's no shortcut to reading all of the data. Can I ask why a to_addr, which is presumably limited to ~320 characters by the SMTP standards (depending on which standard you believe), contains data > 4000 characters?

A lot of people think that a scan is bad. If you need to return a large amount of data, then often a clustered index scan will be used. Your where clause may lead to seeks being used to locate the rows to return, but a seek isn't going to work where the data in that column is that large. Are you just seeing a scan in the execution plan and assuming that must be the problem?