Monday, May 29, 2017

Creating Full-Text Catalog in SQL Server 2016 SP1

A full-text catalog is a logical container where a group of full-text indexes are stored. A full-text catalog is needed before you can create a full-text index. Full-text catalog is a virtual object and does not belong to the file group.

In order to text search a full-text index will be needed. For example, the Categories table in Northwind does not have a full-text indexed column. If you try to create a Full-Text indexed column you will get this error:
-----------
Msg 9967, Level 16, State 1, Line 11
A default full-text catalog does not exist in database 'Northwind' or user does not have permission to perform this action.

--------------

You can create a Full-text catalog using the SQL Server Management Studio or using Transact-SQL(T-SQL)
---------
Here is how it is created for Northwind database in SQL Server 2016 using T-SQL

USE Northwind
Go
Create FULLTEXT CATALOG ftCat_Nwind as default
Go

------
Before the above statement was run there was no Catalog:


After the above statement was processed the named catalog was created:


You can access the properties as shown here

All tables and views are eligible and you can assign the objects to the Catalog by transferring using the > button.

Creating a Full-Text catalog for Northwind using SQL Server Management Studio is easy. Right click Full-Text Catalogs in the Storage node and provide a name for the catalog and a owner. Choose the accent sensitivity and click OK to create.


The Catalog gets created as shown.

Is this the end of the story? No

Caution:
Although SQL Server 2016 SP1 allows you to create a Full-Text Catalog, you will find it missing in the System Views as in the following query.

This feature has not improved even from the older versions. It has not been fixed and the error messages that you get such as the following does not give a clue.

Property IsAccentSensitive is not available for the Full-Text catalog <catalog name>
Property PopulationStatus is not available for FullTextCatalog <catalog name>

The reason for this error is that the Full-Text feature was not installed in the first place.
Watch the video and review features not installed:

https://hodentekmsss.blogspot.com/2017/05/installing-sql-server-2016-sp1-for.html

No comments:

DMCA.com Protection Status