Implementing Full-Text Search on View

Full Text Index is used to perform queries to find-out the character data.  These queries can include word or phrase searching.

We can create a full-text index on a table or indexed view in the database. Only one full-text index is allowed per table or indexed view.

Creating Sample tables and Data

We have created two tables named Tb_Category and Tb_DataType

IF OBJECT_ID('TB_Category','U') IS NULL
CREATE TABLE TB_Category
(
CategoryID INT IDENTITY(1,1),
Category VARCHAR(50) NOT NULL
)
GO

IF OBJECT_ID('TB_DataType','U') IS NULL
CREATE TABLE TB_DataType
(
DataTypeID INT IDENTITY(1,1),
DataType VARCHAR(20) NOT NULL,
CategoryID INT NOT NULL
)
GO

INSERT TB_Category(Category) VALUES('Exact Numerics')
INSERT TB_Category(Category) VALUES('Approximate Numerics')
INSERT TB_Category(Category) VALUES('Date and Time')
INSERT TB_Category(Category) VALUES('Character Strings')
INSERT TB_Category(Category) VALUES('Unicode Character Strings')
INSERT TB_Category(Category) VALUES('Binary Strings')
INSERT TB_Category(Category) VALUES('Other Data Types')
GO

INSERT TB_DataType(DataType,CategoryID) VALUES('Bigint',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Decimal',1) 
INSERT TB_DataType(DataType,CategoryID) VALUES('Int',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Numeric',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Smallint',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Money',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Tinyint',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Smallmoney',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Bit',1)
INSERT TB_DataType(DataType,CategoryID) VALUES('Float',2)
INSERT TB_DataType(DataType,CategoryID) VALUES('Real',2)
INSERT TB_DataType(DataType,CategoryID) VALUES('Datetime',3)
INSERT TB_DataType(DataType,CategoryID) VALUES('Smalldatetime',3)
INSERT TB_DataType(DataType,CategoryID) VALUES('Char',4)
INSERT TB_DataType(DataType,CategoryID) VALUES('Text',4)
INSERT TB_DataType(DataType,CategoryID) VALUES('Varchar',4)
INSERT TB_DataType(DataType,CategoryID) VALUES('Nchar',5)
INSERT TB_DataType(DataType,CategoryID) VALUES('Ntext',5)
INSERT TB_DataType(DataType,CategoryID) VALUES('Nvarchar',5)
INSERT TB_DataType(DataType,CategoryID) VALUES('Binary',6)
INSERT TB_DataType(DataType,CategoryID) VALUES('Image',6)
INSERT TB_DataType(DataType,CategoryID) VALUES('Varbinary',6)
INSERT TB_DataType(DataType,CategoryID) VALUES('Cursor',7)
INSERT TB_DataType(DataType,CategoryID) VALUES('Timestamp',7)
INSERT TB_DataType(DataType,CategoryID) VALUES('Sql_variant',7)
INSERT TB_DataType(DataType,CategoryID) VALUES('Uniqueidentifier',7)
INSERT TB_DataType(DataType,CategoryID) VALUES('Table',7)
INSERT TB_DataType(DataType,CategoryID) VALUES('Xml',7)
GO

CREATE VIEW VM_DataTypes WITH SCHEMABINDING AS
SELECT C.Category,D.DataType FROM DBO.TB_Category C JOIN DBO.TB_DataType D
ON(C.CategoryID = D.CategoryID)
GO

CREATE UNIQUE CLUSTERED INDEX UCI_DataType ON VM_DataTypes(DataType)
GO

When creating Unique Indexes on a View, The following are the key points to be followed

1. View should be 'WITH SCHEMABINDING', Otherwise the following Err occurred
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VM_DataTypes' because the view is not schema bound.

2. All tables should be used with schema name (Two part-naming convension, "SchemaName.TableName"), Otherwise the following Err occurred
Msg 4512, Level 16, State 3, Procedure VM_DataTypes, Line 2
Cannot schema bind view 'VM_DataTypes' because name 'TB_Category' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

3. Table hint should not be used (WITH NOLOCK), Otherwise the following Err occurred
Msg 10140, Level 16, State 1, Line 1
Cannot create index on view 'DotNetFunda.dbo.VM_DataTypes' because the view contains a table hint. Consider removing the hint.

4. OUTER Join can not be used Inside the View
5. Non-Deterministic & Windows function cannot be used inside the View
6. The Unique Index should be created on single column, Not with composite, Non-Nullable

Creating Full-Text Catalog

Expand the Database which you want to enable Full-Text search

Expand the Database --> Expand the Storage node --> Right click on FullText Catalog and Click the New Full-Text Catalog..., The following wizard will appears

Give the Full Text catalog name which you want to create as given below and click the OK button.

Creating Full-Text Index on View

Once created the Full Text catalog, Expand the Views node, because we going to create Full-Text index on View and proceed as given below.

Select the all columns which you want to perform search on and choose the language for each columns, In which language you want to search the data.

Select the Full-Text catalog on which you want to create the Full-Text index

Start the SQL Server Full-Text Filter Daemon Launcher Service as given below.

Control Panel --> Administrative Tools --> Services -->

This is the actual data from the View

If we want to serach the word Approximate and Int on all the columns on View

SELECT * FROM VM_DataTypes WHERE FREETEXT(*,'approximate int')

SELECT * FROM VM_DataTypes WHERE FREETEXT(*,'Exact Date Binary')

If we want to serach the word Numerics and Varchar on all the columns on View

Conclusion