Defining full-text Indexes

Before you can specify which columns will make up the full-text index, you must first enable the database and the table itself that will contain the columns.
To implement Full-Text Indexing using SQL Server Management Studio (SSMS) in SQL Server 2022, follow these steps. SSMS provides a GUI-based approach that makes the process visual and intuitive, especially for those new to full-text search. ✅ Prerequisites

1. Full-Text Search must be installed during SQL Server setup.
2. The target table must:
   • Contain character-based columns (varchar, nvarchar, etc.)
   • Have a unique, non-nullable single-column index (usually the primary key)

🔧 Step-by-Step Guide in SSMS

1. Launch SSMS and Connect to the Server
   • Open SQL Server Management Studio
   • Connect to your SQL Server 2022 instance
2. Expand the Target Database
   • In Object Explorer, navigate to:

     Databases → YourDatabase → Tables
             

3. Right-click the Target Table → Choose Full-Text index → Define Full-Text Index...
   • This will launch the Full-Text Indexing Wizard.
4. Step Through the Wizard
   • Welcome Screen
     • Click Next
   • Select Unique Index
     • Choose a unique, non-nullable index (often the primary key)
     • Click Next
   • Select Columns
     • Check the boxes for the text columns you want to include in the full-text index
     • Optionally, choose a language (e.g., English – 1033)
     • Click Next
   • Select Catalog
     • Choose an existing full-text catalog or create a new one
     • Example: Emp_Notes
   • Change Tracking
     • Choose how the full-text index should update:
       • Automatic (default)
       • Manual
       • Do not track changes
   • Populate Index
     • Select “Start full population” if you want the index built immediately
5. Review Summary and Click Finish
   • The wizard will execute the required CREATE FULLTEXT INDEX statements in the background
   • Once complete, you'll see a confirmation

Using SSMS:
* Expand:

  Tables → [YourTable] → Full-Text Indexes
  

* You should see the newly created full-text index
Using T-SQL:

SELECT * 
FROM sys.fulltext_indexes
WHERE object_id = OBJECT_ID('dbo.YourTable');

🛠️ Example of What the Wizard Does Internally
Here’s the equivalent T-SQL code:

CREATE FULLTEXT CATALOG Emp_Notes;

CREATE FULLTEXT INDEX ON Employees
(
    Notes LANGUAGE 1033
)
KEY INDEX PK_Employees
ON Emp_Notes
WITH CHANGE_TRACKING AUTO;

✅ Optional: Test with a Full-Text Query

SELECT * 
FROM Employees
WHERE CONTAINS(Notes, 'performance OR leadership');

The Full-Text Indexing Wizard in SQL Server Management Studio (SSMS) allows you to perform all three of those tasks, though with some caveats regarding terminology and where certain actions happen:

1. Enable a database for full-text searching

   Answer: ✔️ Yes (implicitly)

   • When you create your first full-text index via the wizard, SQL Server automatically enables full-text search for the database (if not already enabled).
   • You do not need to manually enable it.
   • You can verify this afterward with:

      SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsFullTextEnabled');
                     

2. Select tables used in a full-text catalog

   Answer: ✔️ Yes

   • During the wizard:
     • You right-click a specific table and launch the Full-Text Indexing Wizard.
     • That table is then registered as being part of the selected or new full-text catalog you define in the wizard.
   • You can create multiple full-text indexes across tables, each associated with the same or different full-text catalogs.

3. Select columns within those tables used in a full-text catalog

   Answer: ✔️ Yes

   • In the "Select Columns" step of the wizard:
     • You explicitly choose which columns to index.
     • Only character-based columns (varchar, nvarchar, text, etc.) will be selectable.
     • You can also assign a language ID per column (e.g., English = 1033).

Action	Supported in Wizard?	Notes
Enable a database for full-text search	✔️ Yes (automatically)	No manual step needed
Select specific tables to participate in full-text indexing	✔️ Yes	Via right-click on table
Select specific columns from those tables for full-text indexing	✔️ Yes	Only character-based types

Defining a full-text index with Transact-SQL is quite a bit different than defining a relational index since there is no specific syntax as provided by SQL. The syntax for defining a full-text index is done by using system stored procedures. There is a direct relationship between defining full-text indexes and system stored procedures in SQL Server, especially if you are working with legacy or script-based configurations.
✅ Two Ways to Define Full-Text Indexes

1. Modern Approach (Preferred)

   • Use the standard CREATE FULLTEXT INDEX Transact-SQL statement.
   • Used in SQL Server 2005+, including SQL Server 2022.
   • Fully integrated with the SQL engine and SSMS UI.

   CREATE FULLTEXT INDEX ON Employees
   (
       Notes LANGUAGE 1033
   )
   KEY INDEX PK_Employees
   ON Emp_Catalog
   WITH CHANGE_TRACKING AUTO;
   

2. Legacy Approach (Using System Stored Procedures)

   • These system stored procedures were used in SQL Server 2000 and earlier for managing full-text search components.
   • They are still available for backward compatibility in SQL Server 2022 but are not the preferred method.

Here are the most relevant ones:

System Stored Procedure	Purpose
sp_fulltext_catalog	Creates, drops, or modifies a full-text catalog
sp_fulltext_table	Associates a table with a full-text catalog
sp_fulltext_column	Registers or unregisters a column for full-text indexing
sp_fulltext_service	Configures full-text engine-level settings
sp_fulltext_database	Enables/disables full-text search for a database
sp_help_fulltext_system_components	Displays info about filters, word breakers, etc.

📌 Example (Legacy Style)

EXEC sp_fulltext_catalog 'Emp_Catalog', 'create';
EXEC sp_fulltext_table 'Employees', 'create', 'Emp_Catalog', 'PK_Employees';
EXEC sp_fulltext_column 'Employees', 'Notes', 'add';

⚠️ Recommendation

Use Case	Recommended Approach
New development in SQL 2022	CREATE FULLTEXT INDEX
Scripting or backward compatibility	sp_fulltext_* procedures (if needed)

🧠 Final Note The system stored procedures offer a procedural interface to what is now declaratively managed via T-SQL DDL (`CREATE`, `ALTER`, `DROP`) and the SSMS GUI.

The SQL Configuration Object Stored Procedures Protocol specifies an interface for protocol clients to store and retrieve configuration settings on a protocol server, and to efficiently maintain a distributed cache of those settings by querying for settings that have changed on the protocol server. Relationship to Other Protocols The following diagram shows the transport stack that the protocol uses:

Modernized version of both the code and the explanation for SQL Server 2022 best practices :
✅ 1) Updated Code (SQL Server 2022 Best Practices)

-- Switch to the DT database context
USE [DT];
GO

-- Enable full-text indexing for the DT database
EXEC sp_fulltext_database @action = 'enable';

📘 2) Updated Explanation (SQL Server 2022)
In SQL Server 2022, enabling full-text indexing at the database level is the first required step before creating full-text indexes on tables. The stored procedure `sp_fulltext_database` is used with the `@action` parameter set to `'enable'`, which allows full-text catalogs and indexes to be created within the specified database.
> ✅ Best Practice: Always wrap procedure calls using named parameters (like `@action = 'enable'`) for better readability and maintainability, especially in scripts that may evolve over time.