SQL Server

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

Contributors

Vivasoft Team

Tech Stack

0 +

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.

1) 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

2) 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

4) Create a FULLTEXT INDEX on VIEW CustomerInfoIndexView

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

5) Test again and see the result

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

6) 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 part-1

To get a tailored application hire Developers from us

Find Your Talent

7) To get better benefits we can combine both FREETEXT and CONTAINS. But Suffix searches are not supported in CONTAINS, they only support Prefix.

				
					SELECT * FROM CustomerInfoIndexView WHERE FREETEXT(*,'Gigi Butl')
UNION
SELECT * FROM CustomerInfoIndexView WHERE CONTAINS(*,'"Gig*" OR "Butl*"' )
GO