Full-Text Searching - FTS - Sql Server 2008 R2

20 August 2010 · Sql Server 2008 · Article · 91 visite

Learn how to use SQL Server’s full-text searching capabilities to perform sophisticated data querying and selection.

Understanding Full-Text Searching

Wildcard Filtering Limitation
· Performance: Like operator searches can be very time-consuming because Sql Server try to match for each row in the table
· Explicit control
· Intelligent results: the wildcard searching doesn’t provide an intelligent way to select the results (single match and multi matches are not distinguished.

Full text searching does not need to look at each row in the table because an index of the words (in a coloumn of the table) is created in Sql Server.

Setting Up Full-Text Searching

Prerequisite for the full text searches:
· Enable Full Text Search in the database:
· Define a catalog (where full text data are stored)
· Create an index for indexes coloumns and tables.
After indexing you can use in your SELECT the predicates:
· FREETEXT
· FREETEXTTABLE
· CONTAINS
· CONTAINSTABLE
· etc

Enabling Full-Text Searching Support

Enable full text searches
To enable full text searches for your database execute the following script:

EXEC sp_fulltext_database 'enable';
Creating a Full-Text Catalog
CREATE FULLTEXT CATALOG [MaragnaNetFullTextCatalog]WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION [dbo]
Creating a Full-Text Index
CREATE FULLTEXT INDEX ON Contents(ContentTitle,ContentDescription)
KEY INDEX PK_Contents
ON [MaragnaNetFullTextCatalog];
Where Contents is the table, ContentTitle and ContentDescription are the coloumns and PK_Contents is the table primary key name.
Populate the index
ALTER FULLTEXT CATALOG [MaragnaNetFullTextCatalog] REBUILD;
Performing Full-Text Searches
SELECT *
FROM Contents
WHERE contains(contenttitle, 'ado and net');
SELECT *
FROM Contents
WHERE FREETEXT(contenttitle, 'ado GUID');
SELECT *
FROM Contents
WHERE CONTAINS(contenttitle, ' "ado*"');

Full-Text Searching - FTS - Sql Server 2008 R2

Navigazione

Contatti