module 8: querying full-text indexes. overview introduction to microsoft search service microsoft...
TRANSCRIPT
Module 8: Querying Full-Text Indexes
Overview
Introduction to Microsoft Search Service
Microsoft Search Service Components
Getting Information About Full-Text Indexes
Writing Full-Text Queries
Introduction to Microsoft Search Service
Provides Text-based Searches in SQL Server
Complex searches on unstructured text data for words and phrases
More powerful than LIKE operator and wildcards
Available in Other Microsoft Products
Indexing Service, Exchange 2000, Commerce Server
Microsoft Search Service Components
Search Service Structures
Full-text indexKeeps track of significant words used in a tableRequires unique key column or primary key
Full-text catalogIs a collection of full-text indexes
Microsoft Search Service Functions
Indexing Querying
Getting Information About Full-Text Indexes
Using System Stored Procedures sp_help_fulltext_catalogs sp_help_fulltext_tables sp_help_fulltext_columns
Using Transact-SQL Functions
Use Transact-SQL functions to obtain information about full-text properties
USE northwindSELECT DATABASEPROPERTY('Northwind','IsFullTextEnabled')GO
USE northwindSELECT DATABASEPROPERTY('Northwind','IsFullTextEnabled')GO
Writing Full-Text Queries
CONTAINS Predicate
FREETEXT Predicate
CONTAINSTABLE and FREETEXTTABLE Functions
Using Full-Text Keywords
CONTAINS Predicate
Inflectional Form of a Specific Word
Word or Phrase Beginning with Specific Text
Word or Phrase near Another Word or Phrase
One or More Specific Words and Phrases
Words or Phrases with Weighted Values
SELECT plant_id, common_name, price FROM plants WHERE CONTAINS( *, ' "English Thyme" ' )GO
SELECT plant_id, common_name, price FROM plants WHERE CONTAINS( *, ' "English Thyme" ' )GO
Example 1Example 1
FREETEXT Predicate
Searches on Words, Phrases, and Sentences Extracted from Initial Query
Less Precise Than the CONTAINS Predicate
SELECT * FROM news_table WHERE FREETEXT( description, '"The Fulton County Grand Jury said Friday an investigation of Atlanta‘s recent primary election produced no evidence that any irregularities took place."')GO
SELECT * FROM news_table WHERE FREETEXT( description, '"The Fulton County Grand Jury said Friday an investigation of Atlanta‘s recent primary election produced no evidence that any irregularities took place."')GO
CONTAINSTABLE and FREETEXTTABLE Functions
Using CONTAINSTABLE and FREETEXTTABLE Return a table Return column that contains full-text key values top_n_by_rank argument
Choosing Which Search Condition or Function to Use
Use the CONTAINS and FREETEXT predicates in any search condition
Use the CONTAINSTABLE and FREETEXTTABLE functions in the FROM clause
Using Full-Text Keywords
USE pubsSELECT T.title, P.pub_name FROM publishers AS P INNER JOIN titles AS T ON P.pub_id = I.pub_id WHERE P.pub_id = (SELECT pub_id FROM pub_info WHERE CONTAINS
(pr_info, ' moonbeam AND ontario AND "flying saucer" '))GO
USE pubsSELECT T.title, P.pub_name FROM publishers AS P INNER JOIN titles AS T ON P.pub_id = I.pub_id WHERE P.pub_id = (SELECT pub_id FROM pub_info WHERE CONTAINS
(pr_info, ' moonbeam AND ontario AND "flying saucer" '))GO
USE northwindSELECT Description FROM Categories WHERE CategoryName <> 'Seafood‘ AND CONTAINS(Description, ' sauces AND seasonings ')GO
USE northwindSELECT Description FROM Categories WHERE CategoryName <> 'Seafood‘ AND CONTAINS(Description, ' sauces AND seasonings ')GO
CONTAINS and FREETEXT PredicatesCONTAINS and FREETEXT Predicates
CONTAINS Within a SubqueryCONTAINS Within a Subquery
Use Full-Text Indexes on CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT and IMAGE Data TypesUse Full-Text Indexes on CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT and IMAGE Data Types
Use the Full-Text Index and Catalog Properties for TroubleshootingUse the Full-Text Index and Catalog Properties for Troubleshooting
Use the top_n_by_rank Argument to Restrict Result Set SizeUse the top_n_by_rank Argument to Restrict Result Set Size
Recommended Practices
Lab A: Querying Full-Text Indexes
Review
Introduction to Microsoft Search Service
Microsoft Search Service Components
Getting Information About Full-Text Indexes
Writing Full-Text Queries