Full Text Searching  «Prev  Next»
Lesson 4 Defining full-text indexes
Objective Define full-text indexes.

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 indexDefine 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

🔍 Verify the Index
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');


Full-Text Indexing Wizard in SQL Server Management Studio (SSMS)

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).
🧠 Summary Table
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



Relationship between 1) defining full-text indexes and 2) System Stored Procedures?

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:
This protocol in relation to other protocols
This protocol in relation to other Protocols

Prerequisites and Preconditions of SQL Server Object Stored Procedure

What are the prerequisites and preconditions for the SQL Server Object known as Stored Procedure? In essence, the "prerequisites" for stored procedures boil down to having a functional SQL Server environment and the necessary SQL knowledge and permissions. The "preconditions" are more about best practices that lead to well-designed, secure, and performant stored procedures.


Use the following series of images below to follow the steps that you must take to define your full-text catalogs as shown in the above table. In this example, assume that the Employees table has a Notes column that is a text datatype.
The Employees table is in the DT database.

Full-text index in SQL-Server
1) The first parameter for the sp_fulltext_database system stored procedure is enable, indicating that we will enable the database for full-text searching.
First, let us enable the DT database for full-text indexing with the following Transact-SQL statements:*
USE DT  
GO  
sp_fulltext_database 'enable'  

1) The first parameter for the sp_fulltext_database system stored procedure is enable, indicating that we will enable the database for full-text searching.

Rewrite for the 1)code and 2) explanation for SQL Server 2022 best practices.
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.

2) The first parameter for the sp_fulltext_catalog system stored procedure is the name of the full-text catalog, Emp_Notes. The second parameter is the action to perform. In this case, we are creating the full-text catalog. We could have specified a root directory, but it is optional.
2) Next, we need to create the full-text catalog that will be used in the table. We’ll call the catalog Emp\_Notes. Create the catalog with this Transact-SQL:
sp_fulltext_catalog 'Emp_Notes', 'create'

The first parameter for the sp_fulltext_catalog system stored procedure is the name of the full-text catalog, Emp_Notes. The second parameter is the action to perform. In this case, we are creating the full-text catalog. We could have specified a root directory, but it is optional.

3) The first parameter is the table name, Employees, that is to have the index created. The second parameter is the action to perform. Because we are creating the index, the create action is specified. The third parameter is the name of the index to be created within the catalog, Emp_Notes. The fourth parameter is the name of the unique or primary key index that is used to build the full-text index. The example above is the primary key index, called PK_Employees.
3) Now, let’s create the index used for full-text searching in the Employees table, using the catalog created earlier with this statement:*
sp_fulltext_table
Employees, 'create', 'Emp_Notes', 'PK_Employees'
3) The first parameter is the table name, Employees, that is to have the index created. The second parameter is the action to perform. Because we are creating the index, the create action is specified. The third parameter is the name of the index to be created within the catalog, Emp_Notes. The fourth parameter is the name of the unique or primary key index that is used to build the full-text index. The example above is the primary key index, called PK_Employees.

4) The first parameter is the table name that contains the full-text index that you wish to add a column to. In this case, we are adding the column to the Employees table. Next, the second parameter is the column name to add. The above code adds the Notes column in the Employees table to the full-text index. The third parameter indicates the action to perform, add.
4) The first parameter is the table name that contains the full-text index that you wish to add a column to. In this case, we are adding the column to the Employees table. Next, the second parameter is the column name to add. The above code adds the Notes column in the Employees table to the full-text index. The third parameter indicates the action to perform, add.

5) The first parameter is the table name, Employees, that contains the full-text index you wish to activate. The second parameter is the action to be performed. Because we wish to activate the full-text searching capabilities and all other steps have been performed, we specified the action parameter of activate.
5)
sp_fulltext_table Employees, 'activate'
The first parameter is the table name, Employees, that contains the full-text index you wish to activate. The second parameter is the action to be performed. Because we wish to activate the full-text searching capabilities and all other steps have been performed, we specified the action parameter of activate.

In the next lesson, we will write queries to access full-text catalogs.
SEMrush Software