Indexing Strategy for NVARCHAR(MAX) Columns

Hi all,

First of all, I am trying to parse Exchange 2007 Message Tracking log files straight into SQL for querying/trending information. So far I am making good progress.

I am wondering what is the best strategy for indexing some of my columns that use the NVARCHAR(MAX) data type?

I have created a CLUSTERED INDEX on my Primary Key column which is an auto-incrementing identity column that adds 1 to the ID each time.

I have also created some NONCLUSTERED INDEXES on a few of my other columns such as [message-id], [sender-address] etc.

The columns in question are ones which can range from being very small to very large in size, and I have no real control over the size of the columns. These are the columns I am questioning about:

[message-subject] NVARCHAR(MAX)
[recipient-address] NVARCHAR(MAX)

My table name is MessageTracking.

Users will need to be able to query for content potentially in the middle of the strings, and I know that if you use a normal index that you can't do queries like 'WHERE Something LIKE '%SomeValue%' as it won't use any indexes if the WHERE clause is prefixed with %.

I was thinking should I create a FULL TEXT index on these columns? currently my largest data value in the columns is only around 130 characters long but i've seen message-subjects of around 300 characters in length and recipient-address values larger than that.

Need any more info then please ask and I will try and provide info.

Thanks!