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
