I am working on a web-based Customer Relationships Manager (CRM) project. We have about 5,000 active users whose information is stored in a single SQL Server database.

Users have the ability to add dated notes to their contacts. These notes are typically about a page long. There are roughly 3,000,000 notes between all of the active and inactive user accounts.

The notes table has an integer primary key, a note column, which is a varchar(max), an integer User ID column, a Contact ID, and a date/time field. It has an index that goes first by the User ID, then by the Contact ID, then by the date/time.

I'd like to give users the ability to do a full-text search on their notes. I've added a full-text index for the note column. I'm worried that the index might not work well for what I want to do, which is to search of a particular user's notes with a particular string inside. I think that it might get all of the notes, across all of the users, first, and on the inner join narrow it down to just the notes for that user. That's what the execution plan is showing. I imagine performance would be abysmal, say if a user searched for something like "faxed", which is in at least one percent of notes, and it first found the 30,000 notes and then narrowed it down to ones that had the current user's User ID.

What I want is to have it index first on the User ID column, and then on the full text of the note column. Does SQL Server 2005 support something like this?