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

,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)   
,[To]=[to_addr]  --I'm a very long Text or NVARCHAR(MAX) Field
,CASE WHEN [attach] = 1 THEN 'Yes' ELSE 'No' END AS 'Att'   
FROM dbo.[email] 

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 !

  • 2
    Fulltext index?
    – Jon Seigel
    Commented May 1, 2012 at 0:15
  • @JonSeigel Damn has it come to that ? lol Commented May 1, 2012 at 0:27
  • 2
    TEXT? You shouldn't be using TEXT anymore. Commented May 1, 2012 at 1:06
  • @AaronBertrand I didn't do it ! Its ISV I changed to NVARCHAR(MAX) is that the correct move ? Data Length is well over 4000 characters. Commented May 1, 2012 at 1:08
  • 2
    Yes, TEXT should be VARCHAR(MAX) and NTEXT should be NVARCHAR(MAX). Commented May 1, 2012 at 1:10

1 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?

  • Wow, good answer. Yes what led me down this path was seeing that when the data was read of the nonclustered index the IO was much less but after dusting my brain off quick this is obviously because it isn't retrieving such a wide row. Good call. Also is there any perfomance benifit to changng TEXT field to varchar(max) ? Commented May 1, 2012 at 1:23
  • 3
    Queries won't magically become faster (if you're pulling 50,000 characters per row, it doesn't really matter what the source data type is), but you gain in a lot of other areas, for example you can interact with VARCHAR(MAX) like a first-class citizen instead of all the limitations with TEXT, and of course your schema will be supported going forward (TEXT/NTEXT/IMAGE will eventually be removed from SQL Server). Commented May 1, 2012 at 1:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.