Implementing Full-Text Search on View

PandianS
Posted by in Sql Server category on for Beginner level | Points: 150 | Views : 441447 red flag
Rating: 5 out of 5  
 2 vote(s)

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.


 Download source code for Implementing Full-Text Search on View

Introduction


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

If we want to search the word Exact, Date & Binary on all the columns on View

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

SELECT * FROM VM_DataTypes WHERE FREETEXT(*,'Numerics Varchar')

Conclusion


The Full-Text search performs the searching on all columns on the View / Table which we have choosen. SPACE will be considered as one of the deimiter here.
Page copy protected against web site content infringement by Copyscape

About the Author

PandianS
Full Name: Pandian Sathappan
Member Level: Silver
Member Status: Member,MVP
Member Since: 5/11/2009 2:24:28 AM
Country: India
Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions
http://sqlserverbuddy.blogspot.com/
Microsoft Certification - MCITP: Microsoft Certified IT Professional - SQL Server 2008 Database Administrator - MCTS: Microsoft Certified Technology Specialist - SQL Server 2005 - MCP: Microsoft Certified Professional - SQL Server 2000

Login to vote for this post.

Comments or Responses

Posted by: SheoNarayan on: 9/18/2010 | Points: 10
Fantastic article on Full-Text Search on Views. Excellent work Pandian.

Keep it up!

Regards
Posted by: Ninjasparda on: 10/12/2010 | Points: 10
Hi there
Very useful article indeed, but I just got a question, according to this article http://technet.microsoft.com/en-us/library/ms142497.aspx, A full-text index can include char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, and varbinary(max) columns. but the problem when i was implementing full text search on view is I need to create a unique index for my view first, but the sql server complains that 'Cannot create index on view 'xxxxxxx'. It contains text, ntext, image or xml columns. (Microsoft SQL Server, Error: 1942), since the datatype of one of the column of my table is 'text'. So it looked weird to me, for an index, it is required to not have any of the 'text' data type, but for full-text index, it is ok to have 'text' data type, however, the index is the prerequisite for the full-text index.

Sorry, could you understand what i means? I am struggled to utilize full-text search on my view because one of the column contains ' text' data type

Login to post response

Comment using Facebook(Author doesn't get notification)