Export (0) Print
Expand All

Implementing Full-Text Search

Full-text search is available per instance of SQL Server and is represented in SMO by the FullTextService object. The FullTextService object resides under the Server object. It is used to manage the configuration options for Microsoft Full Text Search service. The FullTextCatalogCollection object belongs to the Database object and it is a collection of FullTextCatalog objects that represent full-text catalogs defined for the database. You can only have one full-text index defined for each table, unlike normal indexes. This is represented by a FullTextIndexColumn object in the Table object.

To create a full-text search service, you must have a full-text catalog defined on the database and a full-text search index defined on one of the tables in the database.

First, create a full-text catalog on the database by calling the FullTextCatalog constructor and specifying the catalog name. Then, create the full-text index by calling the constructor and specifying the table on which it is to be created. You can then add index columns for the full-text index, by using the FullTextIndexColumn object and providing the name of the column within the table. Then, set the CatalogName property to the catalog you have created. Finally, call the Create method and create the full-text index on the instance of SQL Server.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET or How to: Create a Visual C# SMO Project in Visual Studio .NET.

This code example creates a full-text search catalog for the ProductCategory table in the AdventureWorks sample database. It then creates a full-text search index on the Name column in the ProductCategory table. The full-text search index requires that there is a unique index already defined on the column.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Reference the ProductCategory table.
Dim tb As Table
tb = db.Tables("ProductCategory", "Production")
'Define a FullTextCatalog object variable by specifying the parent database and name arguments in the constructor.
Dim ftc As FullTextCatalog
ftc = New FullTextCatalog(db, "Test_Catalog")
ftc.IsDefault = True
'Create the Full Text Search catalog on the instance of SQL Server.
ftc.Create()
'Define a FullTextIndex object varaible by supplying the parent table argument in the constructor.
Dim fti As FullTextIndex
fti = New FullTextIndex(tb)
'Define a FullTextIndexColumn object variable by supplying the parent index and column name arguements in the constructor.
Dim ftic As FullTextIndexColumn
ftic = New FullTextIndexColumn(fti, "Name")
'Add the indexed column to the index.
fti.IndexedColumns.Add(ftic)
fti.ChangeTracking = ChangeTracking.Automatic
'Specify the unique index on the table that is required by the Full Text Search index.
fti.UniqueIndexName = "AK_ProductCategory_Name"
'Specify the catalog associated with the index.
fti.CatalogName = "Test_Catalog"
'Create the Full Text Search index on the instance of SQL Server.
fti.Create()


This code example creates a full-text search catalog for the ProductCategory table in the AdventureWorks sample database. It then creates a full-text search index on the Name column in the ProductCategory table. The full-text search index requires that there is a unique index already defined on the column.

{ 
//Connect to the local, default instance of SQL Server. 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 
   //Reference the ProductCategory table. 
   Table tb = default(Table); 
   tb = db.Tables("ProductCategory", "Production"); 
   //Define a FullTextCatalog object variable by specifying 
   //the parent database and name arguments in the constructor. 
   FullTextCatalog ftc = default(FullTextCatalog); 
   ftc = new FullTextCatalog(db, "Test_Catalog"); 
   ftc.IsDefault = true; 
   //Create the Full-Text Search catalog on the instance of SQL Server. 
   ftc.Create(); 
   //Define a FullTextIndex object varaible by supplying the 
   //parent table argument in the constructor. 
   FullTextIndex fti = default(FullTextIndex); 
   fti = new FullTextIndex(tb); 
   //Define a FullTextIndexColumn object variable by supplying 
   //the parent index and column name arguements in the constructor. 
   FullTextIndexColumn ftic = default(FullTextIndexColumn); 
   ftic = new FullTextIndexColumn(fti, "Name"); 
   //Add the indexed column to the index. 
   fti.IndexedColumns.Add(ftic); 
   fti.ChangeTracking = ChangeTracking.Automatic; 
   //Specify the unique index on the table that is required by 
   //the Full Text Search index. 
   fti.UniqueIndexName = "AK_ProductCategory_Name"; 
   //Specify the catalog associated with the index. 
   fti.CatalogName = "Test_Catalog"; 
   //Create the Full Text Search index on the instance of SQL Server. 
   fti.Create(); 
} 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft