Post
Ask Question

SQL Server Full Text Indexes Using Views

SQL Server full-text indexing is rather easy to set up and configure. However, I ran into an issue with my current project. When doing an "AND" search, the query would simply not work. "OR" searches were fine, but "AND" simply returned zero results.
 
Our table looked like the following.
  1. CREATE TABLE [dbo].[Person] (  
  2.     [PersonID]   INT NOT NULL,  
  3.     [Email]      NVARCHAR (255)   NOT NULL,  
  4.     [FirstName]  NVARCHAR (255)   NOT NULL,  
  5.     [LastName]   NVARCHAR (255)   NOT NULL,  
  6.     CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonID] ASC) WITH (FILLFACTOR = 90)  
  7. );  
Our site included a global search text box that allowed the user to type in whatever they wanted including multiple words.  For our requirements, multiple words meant we wanted results that contain all of the words. So if you typed in "Joe Miller", all items must include Joe AND Miller.
 
However, every approach we took using CONTAINS, CONTAINSTABLE, FREETEXT, and FREETEXTTABLE would not work. I ran across this post which provided a workaround.
 
Here is how we addressed the issue. First, create the full-text catalog.
  1. CREATE FULLTEXT CATALOG [GlobalSearchCatalog]  
  2.     WITH ACCENT_SENSITIVITY = ON  
  3.     AUTHORIZATION [dbo];  
  4. GO  
Next, create a view that contains your search information,
  1. CREATE VIEW [dbo].[ParentsView]  WITH SCHEMABINDING  
  2. AS  
  3. SELECT P.PersonID,  
  4.     P.FirstName,  
  5.     P.LastName,  
  6.     P.Email,  
  7.     P.FirstName + ' ' + P.LastName AS FullName  
  8.     FROM dbo.Person P  
  9. GO  
A couple of items to point out. In line 1, the view is schema bound. In line 7, we are creating an aggregated first and last name for the person. This is key to this technique of searching the full text index.
 
Next, you need to create a clustered index on your new view. This can be done because the view is schema bound.
  1. CREATE UNIQUE CLUSTERED INDEX [PK_ParentsView] ON [dbo].[ParentsView]  
  2. (  
  3.     [PersonID] ASC  
  4. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  5. GO  
At this point, you can create the full text index with the following script.
  1. CREATE FULLTEXT INDEX ON [dbo].[ParentsView]  
  2.     ([FullName] LANGUAGE 1033,   
  3.     [Email] LANGUAGE 1033)  
  4.     KEY INDEX [PK_ParentsView]  
  5.     ON [GlobalSearchCatalog];  
  6. GO  
Next, let's make a call using the full text index.
  1. SELECT *  
  2.     FROM Person P  
  3.     INNER JOIN CONTAINSTABLE(ParentsView, *, 'user', 250) AS Key_Tbl ON P.PersonID = Key_Tbl.[Key]  
In this code, we are using CONTAINSTABLE and joining the ParentsView with the Person table. The search string is "user" and the * indicates to use all columns in the full-text index.  
 
This is all well and good, but the objective is to perform an AND search.   So, let's try this,
  1. SELECT *  
  2.     FROM Person P  
  3.     INNER JOIN CONTAINSTABLE(ParentsView, *, 'first user', 250) AS Key_Tbl ON P.PersonID = Key_Tbl.[Key]  
Looks good, right?  Nope. You'll receive a syntax error, "Syntax error near 'user' in the full-text search condition 'first user'.  
 
If you want the full string, you can put quotes around the search condition like this,
  1. SELECT *  
  2.     FROM Person P  
  3.     INNER JOIN CONTAINSTABLE(ParentsView, *, '"first user"', 250) AS Key_Tbl ON P.PersonID = Key_Tbl.[Key]  
But that still doesn't achieve the goal because it's looking for "first user" and not "first" AND "user". So, let's try this:
  1. SELECT *  
  2.     FROM Person P  
  3.     INNER JOIN CONTAINSTABLE(ParentsView, *, 'first and user', 250) AS Key_Tbl ON P.PersonID = Key_Tbl.[Key]  
Success! Now we get results where the word "first" and "user" are contained within the same record.
 
We used SQL Replace to make this coding easier with the following,
  1. SET @Term = REPLACE(@Term, ' ', ' AND ')  
  2.   
  3. SELECT *    
  4.     FROM Person P    
  5.     INNER JOIN CONTAINSTABLE(ParentsView, *, @Term, 250) AS Key_Tbl ON P.PersonID = Key_Tbl.[Key]    
You can adjust your search conditions as necessary for wildcards. The search above will give you results containing the while word "first" and the while word "user." What if you wanted "first*" and "user?"  Translate your term into the following:
 
'"first*" and user'.  Note the quotes surrounding "first*" are necessary in the query.  In code, you can use the following,
  1. SELECT @Term1 = COALESCE(@Term1 + '*" AND "', '"') + items  
  2.     from dbo.SplitString(@Term, ' ')  
  3.       
  4. SET @Term1 = @Term1 + '*"'  
And the SplitString function looks like this.
  1. CREATE FUNCTION [dbo].[SplitString](@String varchar(8000), @Delimiter char(1))       
  2. RETURNS @temptable TABLE (items varchar(8000))       
  3. AS       
  4. BEGIN      
  5.     DECLARE @idx int       
  6.     DECLARE @slice varchar(8000)       
  7.       
  8.     SELECT @idx = 1       
  9.         IF len(@String)<1 or @String is null  RETURN       
  10.       
  11.     WHILE @idx!= 0       
  12.     BEGIN       
  13.         SET @idx = charindex(@Delimiter,@String)       
  14.         IF @idx!=0       
  15.             SET @slice = left(@String,@idx - 1)       
  16.         ELSE       
  17.             SET @slice = @String       
  18.           
  19.         IF(len(@slice)>0)  
  20.             INSERT INTO @temptable(Items) VALUES (@slice)       
  21.   
  22.         SET @String = right(@String,len(@String) - @idx)       
  23.         IF len(@String) = 0 BREAK       
  24.     END   
  25. RETURN      
  26. END  
  27. GO  
I hope this helps. Happy coding!