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.

CREATE TABLE [dbo].[Person] (
[PersonID] INT NOT NULL,
[Email] NVARCHAR (255) NOT NULL,
[FirstName] NVARCHAR (255) NOT NULL,
[LastName] NVARCHAR (255) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonID] ASC) WITH (FILLFACTOR = 90)
);

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.

CREATE FULLTEXT CATALOG [GlobalSearchCatalog]
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo];
GO

Next, create a view that contains your search information,

CREATE VIEW [dbo].[ParentsView] WITH SCHEMABINDING
AS
SELECT P.PersonID,
P.FirstName,
P.LastName,
P.Email,
P.FirstName + ' ' + P.LastName AS FullName
FROM dbo.Person P
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.

CREATE UNIQUE CLUSTERED INDEX [PK_ParentsView] ON [dbo].[ParentsView]
(
[PersonID] ASC
)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]
GO

At this point, you can create the full text index with the following script.

CREATE FULLTEXT INDEX ON [dbo].[ParentsView]
([FullName] LANGUAGE 1033,
[Email] LANGUAGE 1033)
KEY INDEX [PK_ParentsView]
ON [GlobalSearchCatalog];
GO

Next, let's make a call using the full text index.

SELECT *
FROM Person P
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,

SELECT *
FROM Person P
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,

SELECT *
FROM Person P
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:

SELECT *
FROM Person P
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,

SET @Term = REPLACE(@Term, ' ', ' AND ')
SELECT *
FROM Person P
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,

SELECT @Term1 = COALESCE(@Term1 + '*" AND "', '"') + items
from dbo.SplitString(@Term, ' ')
SET @Term1 = @Term1 + '*"'

And the SplitString function looks like this.

CREATE FUNCTION [dbo].[SplitString](@String varchar(8000), @Delimiter char(1))
RETURNS @temptable TABLE (items varchar(8000))
AS
BEGIN
DECLARE @idx int
DECLARE @slice varchar(8000)
SELECT @idx = 1
IF len(@String)<1 or @String is null RETURN
WHILE @idx!= 0
BEGIN
SET @idx = charindex(@Delimiter,@String)
IF @idx!=0
SET @slice = left(@String,@idx - 1)
ELSE
SET @slice = @String
IF(len(@slice)>0)
INSERT INTO @temptable(Items) VALUES (@slice)
SET @String = right(@String,len(@String) - @idx)
IF len(@String) = 0 BREAK
END
RETURN
END
GO

I hope this helps. Happy coding!