[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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.

1 Solution
Yes, using a full text index is the right way to go.
chrismerrittAuthor Commented:
Thought so, just wanted to confirm. Seems to be working good so I will close this one off now. Queries coming back nice and fast at the moment :)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now