Overview of full-text Searching

In SQL Server 2022, full-text searching is a feature used to efficiently query character-based data stored in SQL Server tables. It provides powerful indexing and querying capabilities for natural language-based searches over large amounts of textual data, such as documents, descriptions, or articles.
🔍 What is Full-Text Search?
Full-text search enables:

• Searching for words or phrases in character columns (char, varchar, nchar, nvarchar, text, or ntext)
• Support for linguistic-based searching (e.g., inflectional forms like "run", "running", "ran")
• Advanced queries using operators like AND, OR, NEAR, FORMSOF, and wildcards

⚙️ How Full-Text Search Works

1. Full-Text Catalog: A container for full-text indexes.
2. Full-Text Index: Created on one or more columns in a table; uses a word-based index rather than character-based like a regular index.
3. Full-Text Engine: Runs alongside SQL Server, parsing and indexing textual data.
4. Stoplists and Language Support: Stop words (like "the", "a", "is") are excluded; SQL Server supports multiple languages for linguistic relevance.

Imagine a `Products` table with a `Description` column. You can perform a semantic search for all products containing the word "laptop" near "battery":

SELECT Name, Description
FROM Products
WHERE CONTAINS(Description, 'NEAR((laptop, battery))');

🛠️ Setup Workflow

1. Create an Object Type

   CREATE TYPE department_t AS OBJECT (
     deptno   NUMBER,
     dname    VARCHAR2(100)
   );
   

2. Create an Object Table Based on the Type
   

   CREATE TABLE department_tab OF department_t;
   

3. Create Another Table That Stores REFs

   CREATE TABLE emp_ref_tab (
     empname     VARCHAR2(100),
     dept_ref    REF department_t SCOPE IS department_tab
   );
   

4. Insert Data into the Object Table
   

   INSERT INTO department_tab VALUES (10, 'ACCOUNTING');
   INSERT INTO department_tab VALUES (20, 'RESEARCH');
   

5. Insert REFs into the Ref Table
   

   DECLARE
     d_ref REF department_t;
   BEGIN
     SELECT REF(d) INTO d_ref FROM department_tab d WHERE deptno = 10;
   
     INSERT INTO emp_ref_tab VALUES ('SMITH', d_ref);
   END;
   /
   

6. Write a `DEREF` Query to Access Object Attributes

   SELECT e.empname, DEREF(e.dept_ref).dname AS department_name
   FROM emp_ref_tab e;
   

1. Enable Full-Text Search
   • Use the Full-Text and Semantic Extractions for Search feature during SQL Server installation.
2. Create a Full-Text Catalog (optional in 2022, auto-created if omitted)

   CREATE FULLTEXT CATALOG ProductCatalog;
   

3. Create a Full-Text Index

   CREATE FULLTEXT INDEX ON Products(Description)
   KEY INDEX PK_Products -- Must use a unique index
   ON ProductCatalog;
   

4. Query using Full-Text Functions
   • CONTAINS(): Find rows where text matches specific criteria.
   • FREETEXT(): Find rows where text matches the meaning of a phrase.

• Improved Query Performance via smarter index usage.
• Integration with Azure Cognitive Search for hybrid cloud scenarios.
• Better language detection and stoplist customization.
• Support for columnstore indexes with full-text for analytical workloads.

✅ Summary

Feature	Description
CONTAINS()	Enables exact matches with additional options like NEAR for proximity searches and FORMSOF for inflectional forms of words.
FREETEXT()	Performs semantic, meaning-based searches, allowing for more natural language queries.
Language Support	Applies inflectional and linguistic rules specific to each column and language, improving search accuracy.
Use Cases	Ideal for searching content in various text-heavy fields such as blogs, product descriptions, articles, and customer reviews.
Indexing	Utilizes word-based indexing managed by the Full-Text Index engine for efficient text retrieval.

To use full-text searching, you must make sure that the Microsoft Search service is running. Therefore, this feature is only available if you are running SQL Server on Windows 2012. The Microsoft Search service works on the concept of a full-text catalog. This catalog is used to store special indexes used with the Search service, called full-text indexes. The indexes store all of the significant words used in the fields of a table, as defined by the index.

1. As the data changes, a full-text index is not maintained immediately by SQL Server because it can take a lot of time to update.
2. A full-text index is not automatically updated. You need to manually update it by using stored procedures.
3. A full-text index is not stored in the database, but is stored as a separate file.

In the next lesson, you will learn about the components you should consider when using full-text searching.

Full-text catalogs are mappings of data that speed the search for specifi c blocks of text within columns that have full-text searching enabled. Prior to SQL Server 2008, full-text catalogs were stored external to the database (thus creating some signifi cant backup and recovery issues). As of SQL Server 2008, full-text catalogs have been integrated into the main database engine and storage mechanisms. Due to their complex nature, full-text indexes are beyond the scope of this text.