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

Last Update: March 28, 2024
sql
Table of Contents
Contributors
Picture of Vivasoft Team
Vivasoft Team
Tech Stack
0 +
Want to accelerate your software development company?

It has become a prerequisite for companies to develop custom software products to stay competitive.

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
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
				
			
Potential Developer
Tech Stack
0 +
Accelerate Your Software Development Potential with Us
With our innovative solutions and dedicated expertise, success is a guaranteed outcome. Let's accelerate together towards your goals and beyond.
Blogs You May Love

Don’t let understaffing hold you back. Maximize your team’s performance and reach your business goals with the best IT Staff Augmentation