Indexing Strategy for NVARCHAR(MAX) Columns
Posted on 2011-10-09
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:
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.