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.
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 !