Article Author: Pieter Siegers
Introduction
With the release of Microsoft SQL Server 2005 and its Full Text Search (FTS) support, there is now broad support for creating FTS catalogs and indexes on the fly. While Microsoft SQL Server Management Studio provides for creating them by using a wizard, there’s little control on what’s actually going on, and no feedback is supplied. The sample web application presented in this article provides a much better understanding of the process of creating FTS catalogs and indexes by showing related information on a single web page.
In this series of two articles I’ll introduce you to the basics of using FTS (this article), and the development of a MSSQL FTS Catalog Manager which features three modules (the 2nd article). The first module defines the FTS catalog and index characteristics, the second one let’s you create them and provides you with visual feedback during its creation, and finally, the third one assists in testing the freshly created FTS catalog and its index, and also includes a simple drag and drop query builder.
To give you an idea of what I will be building throughout this article, take a look at Figure 1.
Figure 1. Overview of the sample application
This figure has been reduced in size to fit in the text. To view the full image Click here
Each section of the interface corresponds to a step in the process of creating catalogs and indexes using SQL Server 2005 FTS. This excludes the grey status box on the top right of the web page and the grey messagebox on the bottom of the page.
According to the SQL Server Books Online, the recommended way to generate catalogs and indexes is to use T-SQL (instead of the predefined stored procedures that came with SQL Server 2000), and in the next section I’ll dive right into the details and take a good look at what steps are needed in defining FTS catalogs and indexes, and creating them. You’ll see that I generate dynamic queries, execute them, and display feedback about changed database objects.
System Requirements
The sample web application that accompanies this article is written in C# and requires the following:
- .NET Framework version 2.0
- VS 2005 (or, if you only want to run the web application, then at least the .NET Framework 2.0 runtime is required)
- SQL Server 2005 with FTS installed (local or remote)
Note that SQL Server 2005 Express Edition does not support FTS.
Installing and Compiling the Sample Code
The sample web application included with this article contains a web page that allows you to create a catalog and index using T-SQL.
To install the web application, first download the articles download material and uncompress it into a directory which will create a folder named SQLFTS.
You also need to create a directory on the machine or server where SQL Server 2005 resides. The web.config has an entry for it (named CatBasePath), so be sure to edit the directory’s name and location before you start playing with SQLFTS. The directory serves for storing the catalogs and indexes.
The web.config file also has an entry for the connectionstring (connDB) to the database. Currently a trusted connection is being used per default, but you can change that if you need to.
Now fire up VS 2005 and open the web site using Open | Web Site menu options. In the default configuration, IIS will not be used by VS 2005 (instead, it will use its internal web server), but if you like you can change that using the project’s property pages (Start | Options).
Introduction to SQL Server 2005 Full-Text Search (FTS)
Before FTS indexing came along there was the Indexing Services (IS) which was available on Windows 2000 Server. With IS, you can index large quantities of information on your server’s hard disk. You can then create a web interface that queries the indexes created and displays the results. The Indexing Service wasn’t part of SQL Server however. SQL Server 2000 also provides a full-text search capability, and you can also use the T-SQL’s LIKE commands to query a table column (only text).
All of these have their advantages, but more important are their disadvantages. IS, for example, can take a long time if you need to recreate indexes, and indexes are not that reliable – they corrupted frequently. For example, recovering an index of about 3.5 million records here at my work took about five days – that’s simply too long. IS also created indexes based on content on a disk – you couldn’t use a database table. The full-text search and LIKE options are very limited in their capabilities and do not provide enough functionality in many cases.
When FTS for SQL Server 2005 came along it allows you to create FTS catalogs and indexes on your hard disk by indexing database tables. The indexing engine has been changed to a new service, called msftesql.exe. The catalogs are used to hold a collection of indexes. A catalog can contain one or more indexes. If a catalog contains at least one index, you cannot delete (drop) it; you must always first empty the catalog. You will see this later in the article.
FTS features two main parts: indexing and querying.
Indexing
The indexing part runs as a Windows service, and since SQL Server 2005 (like SQL Server 2000 did) supports multiple instances on one machine, each instance runs its own SQLFTS service. It is capable of indexing database tables at a very high speed. It indexes using batches of data from the table. You can fine-tune the rate of generated batches versus the processed ones. It exposes information about the progress of the creation of an index. Note that we’re talking about FTS indexes and not the normal indexes you can create on each table.
A FTS index can be created only on certain SQL Data Types which are currently: char, varchar, nvarchar, varbinary(max), text, image, nchar, ntext, and xml.
Querying
The querying part comes into play when your catalog is created and contains at least one index. Since this article concentrates on creating catalogs and indexes, it will not be covered here. However the 2nd article will provide more detailed discussion of querying. For now, it suffices to say that once again T-SQL queries are used to query the index. The results are mapped to the actual table and the results are returned just like any other record set, but with additional index table information.
Creation of a FTS Catalog and Index Using T-SQL
The creation of a catalog and FTS indexes can be done with T-SQL. For each step in creating either a FTS catalog or a FTS index, there is a T-SQL query that can be run. To avoid mentioning them twice I’ve chosen to discuss them in the code section below. But first I will provide an overview of the process.
Apart from connecting to a server, to create a catalog, you need to follow a few steps:
- Check to see if FTS is installed on the server
- Check to see if the database you want to use is FTS enabled
- Create the catalog using a T-SQL query
Catalogs are only used as index containers, so to really make use of FTS you need to create at least one index in it. While a FTS catalog doesn’t relate directly to a table, a FTS index does.
To create an FTS index, you need to follow a few additional steps:
- Select a database table
- Check to see if the selected table already contains an FTS index
- If it does, check to see if the FTS index is active (further steps not necessary in this case if active)
- If it doesn’t, select the appropriate table columns
- Select an appropriate indexed column as the key FTS index (see Note below)
- Create the FTS index using a T-SQL query
Note: The appropriate FTS index should preferably be a clustered index and have a type of int. According to the documentation, this should give you the best query performance. See SQL Server Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/8b80390f-5f8b-4e66-9bcc-cabd653c19fd.htm for more detailed information.
This can also be done using the wizard that comes with the SQL Server Management Studio. The web sample application provides you with more flexibility and you can use it from any location that permits you to connect to the database. The web application also provides you a complete overview of the steps needed to create FTS catalogs and indexes while also showing information that the wizard doesn’t provide, and is a guide to understanding the process as a whole.
The steps I mentioned above describe this process and are implemented in the ASP.NET sample web application that is developed throughout the remainder of this article. So lets jump right into the code!
The Code
The code is separated according to the steps described above. We’ll start with some simple steps as indicated above and then get to the more complex and interesting ones.
Connecting to the SQL 2005 Server
If you start up the application, the first thing you need to do is connect to a server, which is shown in Figure 2.
Figure 2. Connecting to SQL Server
This figure has been reduced in size to fit in the text. To view the full image Click here
The gray box on the right is a general label that is used to display messages. In this case it shows the server connection status, and to be clear it is just static information. The code to connect to the SQL Server 2005 server is as follows:
try
{ SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass(); srv.LoginSecure = true; srv.Connect(this.txtSQLSrv.Text.Trim(), "", ""); this.lblConnectTo.Text = "Connected"; if (srv.VersionString.Contains("Express Edition")) { this.lblStatus.Text = "You cannot generate catalogs/indexes on Express – it doesn’t support FTS."; } else { this.lblStatus.Text = "Status: " + Convert.ToString(srv.Status); this.cboDB.Items.Clear(); foreach (SQLDMO.Database db in srv.Databases) { if (db.Name != null) this.cboDB.Items.Add(db.Name); } } Session["InitialCatalog"] = "Initial Catalog=" + this.cboDB.SelectedValue + ";";
}
catch (Exception exc)
{ this.lblStatus.Text = exc.Source + " – " + exc.Message;
}
The version string is being examined first to check to verify that the server version being used is not the Express Edition which doesn’t support FTS. Of course you could include validation for other (earlier) SQL Server versions too, if you wish.
Note that SQLDMO is used to create a SQLServer class, and per default a trusted connection is used.
SQLDMO is a type library that you can use to explore and administrate databases and it’s containing objects. You can add it to your project by adding a reference to it. SQLDMO also provides for connecting to a database using a SQL login and password.
If connected successfully, the list box cboDB is filled with all the database names. The code is executed in a try/catch block, so if any general exception occurs it will be displayed in the gray part where the label lblStatus is located.
Step 1: Seeing if FTS is Installed
Step 1 involves running a simple T-SQL query on the database that was selected in the list box after having connected successfully to the database. Visually it looks like Figure 3:
Figure 3. Check if FTS is installed
This figure has been reduced in size to fit in the text. To view the full image Click here
When you click the Check button, the following code is executed:
try
{ strQuery = "SELECT SERVERPROPERTY;"; if (Session["InitialCatalog"] != null) sa.InitialCatalog = (string)Session["InitialCatalog"]; SqlDataReader sdr = sa.ExecSP(strQuery); while (sdr.Read()) { if (sdr0.ToString() "0") { this.cbFtsInstalled.Checked = false; this.lblStatus.Text = "FTS is not installed - if you use the Express version then FTS is not supported."; } else this.cbFtsInstalled.Checked = true; } } catch (Exception exc) { this.lblStatus.Text = exc.Source + " - " + exc.Message; }
A class named SqlAccess instantiated here as sa is used to handle database access and run the queries that are passed into its method ExecSP(). This is the only functionality that is implemented in the class.
The query that is run, SELECT SERVERPROPERTY('IsFullTextInstalled');, simply queries a server property and returns a '0' if FTS is not installed, or '1' if it is. In the first case I simply cannot continue and the user is notified by showing a message in the status label (the grey area on top left). If FTS is installed then the check box is checked to indicate so.
Step 2: Seeing if the Database is FTS Enabled
The next step is to check if the database selected is FTS enabled. You can enable or disable FTS on each database if you need to. Figure 4 shows this step of the process in the user interface:
Figure 4. Check if the database is FTS enabled
This figure has been reduced in size to fit in the text. To view the full image Click here
The code for the Check button event handler is as follows (only relevant code is shown):
try
{
strQuery = "SELECT DATABASEPROPERTYEX('" +
this.cboDB.SelectedItem.Text + "', 'IsFulltextEnabled');";
SqlDataReader sdr = sa.ExecSP(strQuery);
while (sdr.Read())
{
if (sdr[0].ToString() "0")
this.cbDbIsFtsEnabled.Checked = false;
else
this.cbDbIsFtsEnabled.Checked = true;
}
}
catch (Exception exc)
{
this.lblStatus.Text = exc.Source + " – " + exc.Message;
}
Once again, a T-SQL query is run to query a server property, in this case IsFullTextEnabled. If this property returns ‘0’ it means that the database is not FTS enabled and the check box is thus unchecked. But if it is enabled, it returns a ‘1’, the check box is checked, and we can continue to Step 3. Note that if the database is not FTS enabled you can enable it using either T-SQL or the SQL Server Management Studio.
Step 3: Selecting the Table, Review Details, Selecting the FTS Key Index, and Createing the FTS Catalog
Now things start to get more interesting because here I’ll first select a table to see if it already has a FTS catalog and index related. Note that the web application doesn’t manage multiple indexes, but as I mentioned previously you can have more than one FTS index in a single FTS catalog. In this step I’ll also select the FTS key index to be used and create the FTS catalog in preparation for the next and final step.
The visual part of the web application is principally the yellow part shown in Figure 5. As you’ll see later on it works its way into the other remaining visual blocks too. But first I need to get the tables and show them in a DropDownList for the user to select from.
Figure 5. Selecting Tables and Show Properties
This figure has been reduced in size to fit in the text. To view the full image Click here
When you press the button Show Tables , the following code is executed:
try
{ SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass(); srv.LoginSecure = true; srv.Connect(this.txtSQLSrv.Text.Trim(), "", ""); // match database and fill list with table info for (int i = 0; i < srv.Databases.Count; i++) { if (srv.Databases.Item(i + 1, "dbo").Name this.cboDB.SelectedItem.ToString()) { SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo"); this.lbGetTableInfo.Items.Clear(); for (int j = 0; j < db.Tables.Count; j++) { if (!db.Tables.Item(j + 1, "dbo").Name.StartsWith("sys")) { this.lbGetTableInfo.Items.Add(db.Tables.Item(j + 1,"dbo").Name); } } } } } catch (Exception exc2) { this.lblStatus.Text = exc2.Source + " - " + exc2.Message; }
First the database is matched (I need to do this because in each step I'm connecting at the database server level and thus I need to find the same database that was used in a previous step) and then the tables inside it are listed in the list box lbGetTableInfo. Note that tables that start with sys are system tables, and therefore are not included in the list.
When the list box is filled you can click for example the first table shown, in this case Address (this assumes that the selected database is AdventureWorks). When you select Address, the GetTableInfo() method is executed which will discover the table characteristics related with FTS:
GetTableInfo();
This method does a number of things. First it checks if the table has a clustered index. If so it would be best to use that index when the FTS index is created. The name is shown in a label. If there is no clustered index, then you should first create one, preferably being a single column of type integer. You can do this using SQL Server Management Studio.
if (db.Tables.Item(j + 1, "dbo").HasClusteredIndex)
{
this.cbClusteredIndex.Checked = true;
this.lblClusteredIndex.Text =
db.Tables.Item(j + 1, "dbo").ClusteredIndex.Name;
}
Next it checks if the table already has an FTS index. If so, the checkbox cbTableHasFtsCatalog is checked:
if (db.Tables.Item(j + 1, "dbo").FullTextIndex)
{
this.cbTableHasFtsCatalog.Checked = true;
}
else
{
this.cbTableHasFtsCatalog.Checked = false;
}
If an FTS index already exists, it then fills a label with the name of the index that was used to create the FTS index:
this.lblUniqueIndexForFTS.Text = db.Tables.Item(j + 1, "dbo").UniqueIndexForFullText;
If the FTS catalog already exists, the following code shows the name of the catalog in another label:
this.lblFTSFullCatalogName.Text = db.Tables.Item(j + 1, "dbo").FullTextCatalogName;
If the FTS index already exist, you can check if it is active:
if (db.Tables.Item(j + 1, "dbo").FullTextIndexActive) this.cbFTSIndexActive.Checked = true; else this.cbFTSIndexActive.Checked = false;
Then it fills a drop down list with a list of indexed columns (note that these indexes are normal SQL Server indexes) by using the table's property HasIndex:
if (db.Tables.Item(j + 1, "dbo").HasIndex)
{
this.cboKeyIndex.Items.Clear();
foreach (SQLDMO.Index ind in db.Tables.Item(j + 1, "dbo").Indexes)
{
this.cboKeyIndex.Items.Add(ind.Name);
}
}
Then it executes a query to get the table's rowcount:
strQuery = "SELECT COUNT(*) FROM " + db.Tables.Item(j + 1, "dbo").Owner +
"." + db.Tables.Item(j + 1, "dbo").Name;
if (Session["InitialCatalog"] != null)
sa.InitialCatalog = (string)Session["InitialCatalog"];
SqlDataReader sdr = sa.ExecSP(strQuery);
while (sdr.Read())
{
this.lblRowCount.Text = sdr[0].ToString();
}
Next, the method FillTableColumns() is executed that fills a CheckBoxList with a list of all the columns found in the table:
FillTableColumns();
The code for the method is as follows (only relevant code is shown):
if (db.Tables.Item(j + 1, "dbo").Name this.lbGetTableInfo.Items[this.lbGetTableInfo.SelectedIndex].Text)
{ // fill the checkboxlist and only enable // those if the datatype is FTS indexable int k = 0; this.cblColumnsFTS.Items.Clear(); foreach (SQLDMO.Column col in db.Tables.Item(j + 1, "dbo").Columns) { this.cblColumnsFTS.Items.Add(col.Name.PadRight(30, ‘_’) + col.Datatype + "(" + Convert.ToString(col.Length) + ")"); if (col.Datatype "char" || col.Datatype "varchar" || col.Datatype "nvarchar" || col.Datatype "varbinary(max)" || col.Datatype "text" || col.Datatype "image" || col.Datatype "nchar" || col.Datatype "ntext" || col.Datatype "xml") { if (col.FullTextIndex) this.cblColumnsFTS.Items[k].Selected = true; } else { this.cblColumnsFTS.Items[k].Enabled = false; } k++; } }
The part that that is most important in the code above is where each column is checked for being one that can be included when creating an FTS index. If it is, the row is left enabled, otherwise it is disabled so the user can see the column but cannot select it for inclusion in the FTS index.
After this, the table name and its owner are shown in another label:
this.lblTableName.Text = db.Tables.Item(j + 1, "dbo").Owner + "." + db.Tables.Item(j + 1, "dbo").Name;
Then it simply copies the table rowcount into the peach colored area of step 4 as shown below:
this.lblTableRowCount.Text = this.lblRowCount.Text;
It then sets the catalog name that is displayed in step 4 below:
this.txtCatalogName.Text = "Cat_" + this.cboDB.SelectedValue + "_" + db.Tables.Item(j + 1, "dbo").Owner + "_" + db.Tables.Item(j + 1, "dbo").Name;
And finally it shows the base path where the catalog will be created.
this.lblCatalogBasePath.Text = ConfigurationManager.AppSettings["CatBasePath"];
Make sure that the partition has enough free space for the creation of all the files that participate in the creation of the catalogs and indexes. If your partition runs out of space it is neatly detected by the FTS service.
Each time you select a different table from the listbox, all of the above information is updated.
The next important piece of code is where I need to create a FTS catalog. After selecting a name simply press the button Create FTS Catalog, which executes the following code:
protected void btnCreateCatalog_Click(object sender, EventArgs e)
{
try
{
strQuery = "CREATE FULLTEXT CATALOG [" + this.txtCatalogName.Text + "] "
+ "IN PATH '" + this.lblCatalogBasePath.Text + "' "
+ "WITH ACCENT_SENSITIVITY=OFF";
if (Session["InitialCatalog"] != null)
sa.InitialCatalog = (string)Session["InitialCatalog"];
SqlDataReader sdr = sa.ExecSP(strQuery);
if (sa.LastQueryError != "")
{
this.lbCreationResult.Items.Add(sa.LastQueryError);
this.lbCreationResult.SelectedIndex =
this.lbCreationResult.Items.Count - 1;
}
else
{
// check if CATALOG has been created
strQuery = "SELECT name FROM sys.fulltext_catalogs";
if (Session["InitialCatalog"] != null)
sa.InitialCatalog = (string)Session["InitialCatalog"];
sdr = sa.ExecSP(strQuery);
while (sdr.Read())
{
if (sdr["name"].ToString() this.txtCatalogName.Text)
{
this.lbCreationResult.Items.Add(sdr0.ToString() + " was created
successfully!");
this.lbCreationResult.SelectedIndex =
this.lbCreationResult.Items.Count – 1;
}
}
}
}
catch (Exception exc5)
{
this.lbCreationResult.Items.Add(exc5.Source + " – " + exc5.Message);
this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count
– 1;
}
GetTableInfo();
}
This code also checks afterwards to see if the FTS catalog was actually created by reconnecting and executing a query. The method GetTableInfo() is called afterwards to update any related controls on the page.
Step 4: Selecting Table Columns and Creating the FTS Index
I now have a FTS catalog and can proceed to select the columns that I’d like to include in the FTS index, and then finally I can create the FTS index.
Figure 6 shows what information is displayed about the table (here Address) that was selected in the previous step:
Figure 6. Selecting Table Columns
This figure has been reduced in size to fit in the text. To view the full image Click here
If a catalog or index is not yet created, the next step would be to select the columns that you would like to include in the index. Since the catalog was created in the previous step, I can now proceed to select the columns I want to include in the index and when I’m done with that simply press the button Create FTS Index. The FTS index won’t be created until you select at least one appropriate column and an error is shown if you do not select any column. The code to create an index is as follows:
try
{ System.Text.StringBuilder sb = new System.Text.StringBuilder(); int i = 0; foreach (ListItem li in this.cblColumnsFTS.Items) { if (this.cblColumnsFTS.Items[i].Enabled && this.cblColumnsFTS.Items[i].Selected) { sb.Append("[" + li.Text.Substring(0, li.Text.IndexOf(’_’)) + "], "); } i++; } strQuery = "CREATE FULLTEXT INDEX " + "ON " + this.lblTableName.Text + " (" + sb.ToString().Substring(0, sb.ToString().Length – 2) + ") " + "KEY INDEX [" + this.cboKeyIndex.SelectedValue + "] " + "ON [" + this.txtCatalogName.Text + "] " + "WITH CHANGE_TRACKING AUTO"; if (Session["InitialCatalog"] != null) sa.InitialCatalog = (string)Session["InitialCatalog"]; SqlDataReader sdr = sa.ExecSP(strQuery); if (sa.LastQueryError != "") { this.lbCreationResult.Items.Add(sa.LastQueryError); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1; } else { // check if INDEX has been created strQuery = "SELECT FC.name " + "FROM sys.fulltext_catalogs FC, sys.fulltext_indexes FI " + "WHERE FC.fulltext_catalog_id = FI.fulltext_catalog_id"; if (Session["InitialCatalog"] != null) sa.InitialCatalog = (string)Session["InitialCatalog"]; sdr = sa.ExecSP(strQuery); // read the data while (sdr.Read()) { if (sdr["name"].ToString() == this.txtCatalogName.Text) { this.lbCreationResult.Items.Add("The FULLTEXT INDEX on " + sdr0.ToString() + " was created successfully!"); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1; } } } }
}
catch (Exception exc6)
{ this.lbCreationResult.Items.Add(exc6.Source + " – " + exc6.Message); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1;
}
GetTableInfo();
The code above also checks if the index actually was created. For this it executes a query to get the name of the FTS index, and if it matches the given name, you can assume it was successfully created.
The WITH CHANGE TRACKING AUTO above means that FTS will take care itself of any updates that are done on the underlying table.
If the creation of the catalog and FTS index were done successfully, then you will see something like Figure 7, Figure 8 and Figure 9:
Figure 7. The database section when finished
This figure has been reduced in size to fit in the text. To view the full image Click here
Figure 8. The table section when finished
This figure has been reduced in size to fit in the text. To view the full image Click here
Figure 9. Completion of a successful FTS catalog and index creation
This figure has been reduced in size to fit in the text. To view the full image Click here
The web application also allows for deleting FTS indexes and their catalog containers. Two buttons in steps 3 and 4, named Drop Catalog and Drop FTS Index, are used to do that. Any errors will be displayed in the list box on the right. The code to drop a FTS index is as follows:
try
{ strQuery = "DROP FULLTEXT INDEX ON " + this.lblTableName.Text; if (Session["InitialCatalog"] != null) sa.InitialCatalog = (string)Session["InitialCatalog"]; SqlDataReader sdr = sa.ExecSP(strQuery); if (sa.LastQueryError != "") { this.lbCreationResult.Items.Add(sa.LastQueryError); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1; } else { // check if FTS INDEX has been deleted this.lbCreationResult.Items.Add("FTS INDEX was successfully deleted!"); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1; }
}
catch (Exception exc8)
{ this.lbCreationResult.Items.Add(exc8.Source + " – " + exc8.Message); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1;
}
GetTableInfo();
And finally the code to drop an empty catalog is as follows (remember if the catalog isn’t empty you can’t drop it – an SQL error will be shown if you try):
try
{ strQuery = "DROP FULLTEXT CATALOG " + this.txtCatalogName.Text; if (Session["InitialCatalog"] != null) sa.InitialCatalog = (string)Session["InitialCatalog"]; SqlDataReader sdr = sa.ExecSP(strQuery); if (sa.LastQueryError != "") { this.lbCreationResult.Items.Add(sa.LastQueryError); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1; } else { this.lbCreationResult.Items.Add("CATALOG " + this.txtCatalogName.Text + " was successfully deleted!"); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1; }
}
catch (Exception exc7)
{ this.lbCreationResult.Items.Add(exc7.Source + " – " + exc7.Message); this.lbCreationResult.SelectedIndex = this.lbCreationResult.Items.Count – 1;
}
GetTableInfo();
Both methods check if the FTS catalog or FTS index was actually deleted, by reading the property LastQueryError of the SqlAccess class (named instance sa).
Congratulations, you now have your own catalog and FTS index generator!
Further Work
This web application could be expanded in many ways, for example by adding support for multiple FTS indexes, showing the existing FTS catalogs and their indexes from a database, and showing the queries that are executed. The latter two only require adding some controls to the UI and filling them with content during the process of FTS catalog and index creation.
Adding support for multiple FTS Indexes in one FTS Catalog
The following queries show how you could create a single FTS catalog for a given database, and then create multiple FTS indexes within it. The only difference here is that you do not create a FTS catalog for each FTS index, as was done in the article’s sample application.
CREATE FULLTEXT CATALOG [Cat_AdventureWorks] IN PATH ‘E:\CatMan’ WITH ACCENT_SENSITIVITY=OFF
CREATE FULLTEXT INDEX ON Person.Address (AddressLine1, AddressLine2, City, PostalCode) KEY INDEX [PK_Address_AddressID] ON [Cat_AdventureWorks] WITH CHANGE_TRACKING AUTO
CREATE FULLTEXT INDEX ON Production.Document ([Title], [FileName], [FileExtension], [Revision], [DocumentSummary]) KEY INDEX [PK_Document_DocumentID] ON [Cat_AdventureWorks] WITH CHANGE_TRACKING AUTO
The first query creates the FTS catalog, and the other two queries create each one a FTS index on the same FTS catalog, Cat_AdventureWorks.
Further work will be done in the 2nd article on this topic. There I’ll show you how to develop a SQL 2005 FTS Catalog Manager. So stay tuned!
Conclusion
In this article I introduced SQL Server 2005 FTS, and showed how you can leverage it using ASP.NET (C#) and SQLDMO. I have shown you the complete list of steps needed to successfully create catalogs and FTS indexes. These steps were: connecting to the database, selecting a suitable table, selecting appropriate columns that support FTS indexable SQL data types, selecting a suitable FTS index key, and executing T-SQL queries that finally create the FTS catalog and index.
You can use the sample web application in your every day work to simplify FTS catalog and Index administration tasks.
