SQL Server full-text index using a script

Step-by-step article to create a full-text index on a SQL Server column with all scripts.

Harnessing the power of full-text search can significantly boost the efficiency and speed of your queries in SQL Server. To leverage this feature, one must first create a full-text index on the desired column. This tutorial will guide you step-by-step through the process of setting up a full-text index on a SQL Server column, beginning with table creation and concluding with the index creation itself.

In order to use full-text search on a column, you need to create a Full-Text index on that column. Full text indexes are different from standard SQL Server column indexes. Here’s how you can do that in SQL Server:

1. Create the table to index

First, let’s consider the following SQL Server table.

CREATE TABLE Customers (
   CustomerID INT,
   CustomerName VARCHAR(255),
   Country VARCHAR(255)
);

Then insert a doten lines of data .

INSERT INTO Customers (CustomerID, CustomerName, Country) 
VALUES
   (1, 'John Smith', 'USA'),
   (2, 'Jane Doe', 'Canada'),
   (3, 'Bob Johnson', 'USA'),
   (4, 'Alice Williams', 'UK'),
   (5, 'Charlie Brown', 'USA'),
   (6, 'Sophie Turner', 'UK'),
   (7, 'James Wilson', 'Canada'),
   (8, 'Linda Taylor', 'USA'),
   (9, 'Michael Davis', 'UK'),
   (10, 'Emily Miller', 'Canada'),
   (11, 'William Garcia', 'USA'),
   (12, 'Elizabeth Martinez', 'UK');

2. Create a unique Index on the table

After creating the sample table, you need to ensure that the database has a unique index, which is a requirement for creating a Full-Text index. For the Customers table, assuming CustomerID is unique and not nullable, you can use it:

CREATE UNIQUE INDEX ui_CustomerID 
ON Customers(CustomerID);

3. Create the SQL Server Full-Text catalog

After the unique index is created, you can now create the full-text catalog. A full-text catalog is a logical container for the full-text index:

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

4. Add the Full-Text index to the SQL column

Finally, you can now performe the last step, i.e., create the Full-Text index on the CustomerName column in the Customers table:

CREATE FULLTEXT INDEX ON Customers
(
    CustomerName LANGUAGE English
)
KEY INDEX ui_CustomerID 
   ON ftCatalog 
WITH CHANGE_TRACKING AUTO;

In this query to create the index, these steps and keywords are used:

LANGUAGE English specifies that the column CustomerName is in English language. SQL Server uses this information for word breaking and stemming for example.
KEY INDEX ui_CustomerID specifies the unique index to be used by the Full-Text index.
ON ftCatalog specifies the name of the full-text catalog created in the previous step.
WITH CHANGE_TRACKING AUTO specifies that changes (updates, deletes, or inserts) made to table data, or inserted, are propagated to the full-text index.

Please note that these statements must be executed by a login that has the necessary permissions to create full-text catalogs and indexes, typically a database administrator.

Also, Full-Text Search must be installed and enabled for the database where these commands are run. If the gesture is not installer and active, then the following error message is displayed in SSMS:

Full-Text Search is not installed, or a full-text component cannot be loaded.

Conclusion on full text index creation using T-SQL scripts

To conclude about all these steps and successfully creating a full-text index on a SQL Server column, this process involves several key steps, beginning with the creation of the table and unique index. Then, establishing a full-text catalog, and finally, creating the full-text index itself. Remember, to perform these tasks, having administrator permissions is typically required.

Now, equipped with a valuable tool, the capacity to significantly enhance query performance, especially for text-intensive search operations, is achieved. Always ensure Full-Text Search is installed and enabled for the database to utilise these features.