Advanced Search Using SQL Server Full-Text Search: Part-2

Advanced Search Using SQL Server Full-Text Search: Part-2

In part one, it is shown how to enable basic Full-Text search options on a table in SQL Server. But often our required data is not available in a table that may be available across multiple tables. In this case, we can make a VIEW and then apply the Full-Text search option on it.

In this part, we will try to show how to make VIEW and enable the Full-Text search option on it. In this example we need to search by name and address of customer and required data is available in Person table & Address table. We may not want to search separately, we want at one go.

First, we will check if VIEW already exists or not. If not, then we will create a new one with SCHEMABINDING.

IF OBJECT_ID('CustomerInfoIndexView','V') IS NOT NULL
DROP VIEW CustomerInfoIndexView
GO

CREATE VIEW CustomerInfoIndexView WITH SCHEMABINDING
AS
SELECT
       be.AddressID as Id
      ,pp.FirstName
      ,pp.MiddleName
     ,pp.LastName
      ,[AddressLine1] as [Address]
      ,[City]
FROM [Person].[Address] pa
INNER JOIN [Person].[BusinessEntityAddress] be ON  pa.AddressID = be.AddressID
INNER JOIN Person.Person pp ON be.BusinessEntityID = pp.BusinessEntityID
GO

Now need to create a unique clustered index on this VIEW

IF NOT EXISTS(SELECT 1 FROM sys.indexes
              WHERE name='CIX_CustomerInfoIndexView_Id'
  AND object_id = OBJECT_ID('dbo.CustomerInfoIndexView')
  )
CREATE UNIQUE CLUSTERED INDEX CIX_CustomerInfoIndexView_Id ON dbo.CustomerInfoIndexView(Id)
GO

While running the above query, the system shows an error message. Because we need to create a FULLTEXT INDEX on our VIEW with KEY INDEX.

IF NOT EXISTS(SELECT 1 FROM sys.indexes
  WHERE name='CIX_CustomerInfoIndexView_Id'
 AND object_id = OBJECT_ID('dbo.CustomerInfoIndexView'))
CREATE UNIQUE CLUSTERED INDEX CIX_CustomerInfoIndexView_Id ON dbo.CustomerInfoIndexView(Id)
GO

Create a FULLTEXT INDEX on VIEW CustomerInfoIndexView

CREATE FULLTEXT INDEX ON CustomerInfoIndexView (
  FirstName,
  LastName,
  [Address],
  City
) KEY INDEX CIX_CustomerInfoIndexView_Id
GO

Test again and see the result

SELECT * FROM CustomerInfoIndexView WHERE FREETEXT(*,'Gigi')
GO

Now test again with both FREETEXT & CONTAINS and see the difference

SELECT * FROM CustomerInfoIndexView WHERE FREETEXT(*,'Gig')
GO
SELECT * FROM CustomerInfoIndexView WHERE CONTAINS(*,'"Gig*"')
GO

To know why this result is showing see Advanced Search Using SQL Server Full-Text Search: Part-2