Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 5th, 2007, 03:04 PM
Registered User
Join Date: Sep 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Full Text Indexing split by users

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?

Similar Threads
Thread Thread Starter Forum Replies Last Post
Full Text timing out coww_pie SQL Server 2005 1 October 29th, 2007 11:47 PM
how to enable full text indexing sharvari_mothe SQL Language 1 July 3rd, 2007 09:32 PM
Setting up Full-Text Indexing Aaron Edwards SQL Server ASP 0 July 2nd, 2006 01:32 PM
Setting Up Full-Text Indexing from Visual Studio Aaron Edwards ASP.NET 2.0 Basics 0 July 1st, 2006 01:45 AM
Setting up Full-Text Indexing in Server Explorer Aaron Edwards Visual Studio 2005 0 July 1st, 2006 01:41 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.