Introduction

In the previous article I showed how relatively simple it is to create a new Full Text Search (FTS) catalog and its indexes using T-SQL, C#, and ASP.NET. While it is not mandatory to read that article, I certainly think it helps to explain in clear steps how to define and create FTS catalogs and indexes using T-SQL. It actually lays the groundwork for this article. So I’ll assume you have read that article, and proceed from there, meaning that I will focus more on the SQL Server 2005 FTS catalog and index creation feedback properties, and then the SQL Server 2005 FTS querying.

In this article I’ll present a web application that is divided into three modules as seen in Figure 1.

Figure 1. The three SQL FTS Catalog and Index Manager modules
This figure has been reduced in size to fit in the text. To view the full image Click here

Each module represents an abstract part of the SQL Server 2005 FTS functionality:

• SQL FTS Administration Module


• SQL FTS Catalog and Index Creation Module


• SQL FTS Querying / Testing Module

The first module (SQL FTS Administration Module) is a where you can create FTS catalogs and indexes on the fly. It is similar to the one you saw in the previous article, but more integrated, and the creation part has been left out and moved to the second module. You also can delete existing FTS catalogs and indexes here. The MS Atlas’s UpdatePanel is used to ‘ajaxify’ the web page so that you don’t see the screen flickering anymore (which currently is most apparent in IE). Since this aspect has been discussed in the first article, I’ll not touch on it again here.

The second module (SQL FTS Catalog and Index Creation Module) handles the creation of the FTS catalog and indexes. Only a single FTS index creation is supported, but this could easily be expanded by specifying a different FTS index name and create it in the same FTS catalog. The creation progress is shown real-time by using an AJAX-like ProgressBar that I developed in a previous ASPToday article. I will not discuss the AJAX part here but will go into detail on the Feedback Properties that SQL 2005 FTS provides.

The last module (SQL FTS Querying / Testing Module) is new and permits you to test the FTS index you’ve just created by running queries on it. It features an experimental and simple drag and drop query builder using the basic built-in Windows drag and drop support. Here I’ll focus on the SQL 2005 FTS querying capabilities.

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

Refer to the supplied Readme.txt file located in the download file to get the required dlls, they are not included in the download for this article.

The sample web application (written in C#) included with this article is a web application that shows you how to define, create, and query FTS catalogs and indexes using T-SQL. It uses SQLDMO to retrieve information about the databases and tables being accessed. You need to set a reference to the SQLDMO library to be able to make use of it.

To install the web application, first download the article’s download and unzip it to a directory. You also need to create a (local) directory on the machine or server where SQL Server resides. The web.config has an entry for it named CatBasePath, so if you want to use a different location be sure to edit the directory’s name and location. The directory serves for storing the FTS catalogs and indexes.

Now fire up VS 2005 and open the web site using Open | 463. In the default configuration, IIS will not be used by VS 2005 (it will use its internal web server), but if you like you can change that using the project’s property pages.

FTS Index Querying

Basically what happens when you send a search query to SQL Server it is internally compiled into a full-text query. It then is executed against the FTS index and the resulting list is combined with the underlying database table. The results are returned as a typical record set that you would expect from a normal query, except that you can retrieve extra information such as KEY and/or RANK column, originating from the FTS index table.

Once a FTS catalog contains at least one FTS index you can start querying it. You use the T-SQL query syntax to retrieve search results. There are four ways you can construct queries: FREETEXT, FREETEXTTABLE, CONTAINS, and CONTAINSTABLE. Below are examples of typical queries using each of them.

FREETEXT is a predicate (which means it is used in the WHERE clause), and is used for finding words or phrases that match the meaning of the string being searched for; it doesn’t look for the precise words or phrase.

SELECT Title

FROM Production.Document

WHERE FREETEXT (DocumentSummary, ‘vital safety components’ )

FREETEXTTABLE is like FREETEXT but can be used in the FROM clause as with any ordinary table query. It also features topping by rank. If you don’t use ORDER BY (for example a datetime) this can increase performance considerably. Not using ORDER BY results in unpredictable ordered result sets.

SELECT Title, KEY_TBL.RANK

FROM Production.Document AS FT_TBL 

INNER JOIN

FREETEXTTABLE AS KEY_TBL
   ON FT_TBL.DocumentID = KEY_TBL.[KEY]

CONTAINS can be used for precise word or phrase searches, you can also use it for prefix searches with the * character.

SELECT * 

FROM Person.Address

WHERE CONTAINS;

Finally, CONTAINSTABLE is like CONTAINS but like FREETEXTTABLE you can use it in the FROM clause.

SELECT * FROM Person.Address,

CONTAINSTABLE, ‘ "New York" ‘) T1

WHERE AddressID = T1.[Key]

ORDER BY City ASC

The SQL data types that currently are allowed to participate in a FTS index are: char, varchar, nvarchar, varbinary(max), text, image, nchar, ntext, and xml. Varbinary(max) and image are binary types and they require a TYPE COLUMN set to their respective data type when you include them in a FTS index. Text types are supported to a column length of 900 characters.

FREETEXT and FREETEXTTABLE have a simple syntax and work similarly to the T-SQL LIKE operator, but they work on the FTS index created rather than on the table text columns. The latter adds the possibility to return a record set based on TOP_BY_N_RANK.

CONTAINS and CONTAINSTABLE are more sophisticated, in that they add a set of features that allow you to specify your search query. They allow for: simple_term, prefix_term, generation_term, proximity_term, and weighted_term. You can also use operators like AND ( & ), AND NOT ( &! ), and OR ( | ). You can find examples on how to use each of them in the SQL Server books online that installs with the product.

Because both FREETEXTTABLE and CONTAINSTABLE return a table, they also contain two extra columns: KEY and RANK. This information is stored in the index and is returned to the SQL engine after the index search has been completed. The KEY is the ID of the items stored in the index table. The KEY is mainly used in the WHERE clause to match records found with the underlying SQL table. The RANK is a value that is computed against other results in the index and the underlying table. The RANK value (which value ranges from 0 to 1000) is typically included in the SELECT clause to obtain the ranking information, but can also be used to order the results in the ORDER BY clause. RANK is computed through a complex formula, and has a limitation, it only can operate on one column.

Another big limitation of the querying part is that if you use TOP_BY_N_RANK and use an ORDER BY clause on a datetime column you won’t be able to optimize the search query using the TOP because the ORDER BY requires the creation of a full result table before it is able to return the TOP. In fact, while doing tests with this type of query on a 3+ million record database and its 4GB FTS index, I found out that execution time increased so much that I was forced to split up the indexes over time. From one big FTS index that spanned 20 years I created 20 separate FTS indexes, one for each year, and could thereby reduce the search query execution time by a factor 4.

While most search engines today return results based on RANK, it may be a good thing though to integrate the datetime data type in the FTS index, and its textual representation could be a huge benefit when querying for results ordered in a timely fashion.

In this article I’ll limit the examples to using the CONTAINSTABLE query type, since I have used it more frequently, it gives KEY and RANK information, and has a wider spectrum of search possibilities.

FTS Catalog and Index Creation Feedback

FTS catalog and index creation information is obtained by executing a query on the database and by returning and displaying intermediate information in the browser. I didn’t want to use the old-fashioned META-REFRESH since it refreshes the whole page – that just looks ugly and disturbs users. So I put that option aside. I found a much better solution in using a specific AJAX Library, called Anthem.NET (see the Related Links section for more details), I used MS Atlas for the first module of the web application that accompanies this article, I preferred Anthem.NET in this particular case since it is more flexible regarding the use of JavaScript, and is open-source so you can modify it if something doesn’t work like it should. Anthem.NET integrates fully in the ASP.NET life cycle too, just like MS Atlas, which is an important thing in developing web applications with .NET.

So I used Anthem.NET to initiate a series of callbacks to the web server, which in turn executes repetitively queries on the database server, getting in return information about the progress, which in turn is displayed in the browser without the need to refresh the page. Once the process is terminated, the process of repetitively executing queries on the database also stops and information is displayed. If an error occurs, the process also stops and displays the error (obtained through exception catching) in the browser.

The following FTS index creation properties play a role in giving feedback information during initialization, creation, and finalization of the index creation process. Below is an example of a T-SQL query that you can run during the process that includes all possible properties you may want to obtain:

SELECT AccentSensitivity = FULLTEXTCATALOGPROPERTY
  (‘Cat_AdventureWorks_Person_Address’, ‘AccentSensitivity’),
 IndexSize = FULLTEXTCATALOGPROPERTY (‘Cat_AdventureWorks_Person_Address’,
  ‘IndexSize’), 
 ItemCount = FULLTEXTCATALOGPROPERTY (‘Cat_AdventureWorks_Person_Address’,
  ‘ItemCount’), 
 LogSize = FULLTEXTCATALOGPROPERTY (‘Cat_AdventureWorks_Person_Address’,
  ‘LogSize’), 
 MergeStatus = FULLTEXTCATALOGPROPERTY (‘Cat_AdventureWorks_Person_Address’,
  ‘MergeStatus’), 
 PopulateCompletionAge = FULLTEXTCATALOGPROPERTY
  (‘Cat_AdventureWorks_Person_Address’, ‘PopulateCompletionAge’), 
 PopulateStatus = FULLTEXTCATALOGPROPERTY
  (‘Cat_AdventureWorks_Person_Address’, ‘PopulateStatus’), 
 UniqueKeyCount = FULLTEXTCATALOGPROPERTY
  (‘Cat_AdventureWorks_Person_Address’, ‘UniqueKeyCount’)

Note that the FTS catalog is named Cat_AdventureWorks_Person_Address here, and that the properties are passed in as an argument to the FULLTEXTCATALOGPROPERTY function.

For more information on what the different propertiers mean and what information they return, go to the SQL Server 2005 Books Online and enter: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f841dc79-2044-4863-aff0-56b8bb61f250.htm.

Basically, creating a FTS catalog takes almost no time, setting FTS options neither, but the creation of the FTS index does take time, especially when the table to be indexed is large. The time it takes to create an FTS index is proportional to its size, but the time to start up the FTS indexing process is relatively large for small table sizes. During index creation, start up time the browser shows no sign of progress, but the status line will show a counter that indicates the process is running.

The speed in which FTS can index is hugely increased since the previous version. I personally have managed to decrease index creation time by a factor 26 over a 3+ million table including varchar() and text data types. I have experienced a total of around 750,000 records processed per hour. This varies of course due to mainly hardware capabilities and table composition. Execution time of queries was increased by a factor 200, using the index split-up strategy I described earlier.

The main reason why the querying portion isn’t mature yet is that it doesn’t combine the RANK with normal results, and if it returns RANK it is only based on one column. In addition, be aware that ordering your result set by datetime (using ORDER BY) can considerably slow down query execution time. Yet, the new functionality given is an important step forward and one can only hope that the next release will further extend the search capabilities of SQL Server.

The Sample Web Application

The remainder of this article will be discussing how the sample application works and how to query against an index. As discussed previously the application is broken down into three parts:

• The FTS Catalog and Index Administration Part


• The FTS Catalog and Index Creation Part


• The FTS Index Testing Part

The discussion of the code will thus be broken down into the same three parts in order to make it easier to follow.

Module 1: The FTS Catalog and Index Administration Part

The first module is the Administration Module, where you can connect to the database, select tables, select columns to include in the FTS index, select the index key column to be used. The module additionally lets you delete FTS catalogs and indexes if they already exist. It mainly uses SQLDMO to implement all this functionality. The main benefit of using SQLDMO is that you gain control of an API that represents a complete SQL Server installation, without ever executing one T-SQL query. In the background the calls are actually executed as queries but you don’t have to write them but can instead use the language you’re acquainted with.

A FTS catalog actually is a set of files located in a special working directory. The directory should be located locally, and as you create catalogs they will be put in subdirectories with the name and path you specify. Figure 2 shows a typical directory structure for the catalog created for Person.Address:

Figure 2. Content of a FTS Catalog Directory
This figure has been reduced in size to fit in the text. To view the full image Click here

Module 2: The FTS Catalog and Index Creation Part

Once you have prepared a table, its columns, and the key index to use, you can proceed to the second module, the FTS catalog and index creation module. Here you see information about the FTS catalog and index to be created, and apart from that you’ll see information related to the data that is returned by SQL Server during the FTS index creation. These are the FTS index creation properties we talked about earlier.

This data changes during the process of index creation, and when it finishes, the module detects this and stops polling the database. Normally, a successful index creation process ends with a master merge, where the indexes created in a particular catalog are merged into one big index. Typically the master merge doesn’t take much time in comparison to the index creation time. Master merges occur every 100,000 records processed, or at the end of an indexing process if the table is smaller.

The progress is displayed in the browser as a percentage of the whole process, using a simple ProgressBar. The technique works for all the main browsers, including IE, FF, both on PC, and Safari, on Mac. This is easily achieved using Anthem.NET and simply taking care of writing cross-browser JavaScript.

Module 3: The FTS Index Testing Part

Here we’ve come to the final part where you can test the index that was just created. It is a matter of simply using T-SQL to write queries that run over the index and return record sets. I’ve added a simple experimental drag & drop query builder with items like SELECT that you can drag and drop onto a textbox, and edit from there.

An example query that accesses a FTS index, using CONTAINSTABLE, could be as follows:

SELECT * FROM Person.Address,
  CONTAINSTABLE, ‘ "New York" ‘) T1
  WHERE AddressID = T1.[Key]
  ORDER BY City ASC

The bold portion is where FTS comes into play. CONTAINSTABLE is simply created as a table and assigned an alias of T1. It then is used to join its results with the underlying table, in this case Person.Address. The query as a whole simply returns a record set.

To obtain KEY and RANK information you can use the query below:

SELECT T1.[Key], RANK, * FROM Person.Address,
 CONTAINSTABLE, ‘ "New York" ‘) T1
 WHERE AddressID = T1.[Key]
 ORDER BY City ASC

Note that if you try to use Key instead of T1.[Key] you will get a syntax error, while RANK can be included without using a table alias. The reason why you cannot use Key is because it’s a reserved keyword.

Using the drop and drag query builder is actually simple. It’s really only experimental stuff, but like the example shows, for a start, you can drag the image SELECT to the empty textbox in the middle where it (after dropping it) simply adds the text SELECT. After this, you can add table columns for use in the SELECT clause. The FROM clause follows, the WHERE clause, and you can add for instance an ORDER BY to end the query. At any time you can run the query, if any error arises it will be displayed in the error label located below the textbox.

The Code

The code is simply too extensive to discuss it all in detail here, so I will select the parts that are most interesting for the purpose of this article. Module 1, where the FTS catalog and index are being prepared, has already been discussed in the previous article, along with the code to create them, in the second module. I will however discuss the part of the second module where the T-SQL queries are being constructed, and information is obtained during the FTS index creation. I’ll also discuss the third module where the FTS index is tested through the execution of queries.

Module 2 – The FTS Catalog and Index Creation Part

Before I discuss the code in detail a screenshot of what the module looks like when the FTS index is being created can be seen in Figure 3.

Figure 3. Screenshot module 2: The FTS Catalog and Index Creation Part
This figure has been reduced in size to fit in the text. To view the full image Click here

The creation process consists of the following parts:

• creation of the FTS catalog


• setting FTS options


• creation of the FTS index

For each one of them, a SQL query is built and executed on the database. Typically, the code is similar to the following example (which leads to the code where the query is built to create the FTS Catalog):

private void CreateCatalog()

{
 if (ocm != null)
 {
  string strCatName = ocm.SuggestedCatName;
  string strCatPath = ocm.GetCatBasePath;
  cmpl.GenCatalog(strCatName, strCatPath);
 }

}

The GenCatalog() method looks like this:

public void GenCatalog(string strCatName, string strCatPath)

{
 strQuery = "CREATE FULLTEXT CATALOG [" + strCatName + "] " +
  "IN PATH ‘" + strCatPath + "’ " +
  "WITH ACCENT_SENSITIVITY=OFF;";
 SqlDataReader sdr = sa.ExecSP(strQuery);

}

An example of the query that is actually created in the above code for the Person.Address table is as follows:

CREATE FULLTEXT CATALOG [Cat_AdventureWorks_Person_Address] 
 IN PATH ‘E:\CatMan’ 
 WITH ACCENT_SENSITIVITY=OFF;

The name of the FTS catalog is Cat_AdventureWorks_Person_Address, the catalog path is passed to the parameter PATH, and finally the ACCENT_SENSITIVITY property is set to OFF.

Setting FTS options is optional, and most of them don’t need to be set repetitively. It is relatively simple to do:

public void ConfigFtsOptions(string strOptionName, string strOptionValue)

{
 strQuery = "EXEC [dbo].[sp_configure] ‘" + strOptionName + "’, " + 
   strOptionValue + ";";
 SqlDataReader sdr = sa.ExecSP(strQuery);
 strQuery = "RECONFIGURE;";
 SqlDataReader sdr2 = sa.ExecSP(strQuery);

}

The query built here is like the following example:

EXEC [dbo].[sp_configure] ‘show advanced options’, 1;

The query executes the built-in stored procedure, sp_configure, passing in the name of the option to set and its value to set to. In this case, to be able to set advanced options, you must first set the advanced options to 1. After this, you can set options like transform noise words, precompute rank, and default full-text language.

Creation of the FTS index is definitely the part that interests us most. It starts with generating the query, as shown below:

public string GenIndex(string strTableName, string[,] arrFields, string 
  strIndexKey, string strCatName)

{
 strQuery = "CREATE FULLTEXT INDEX ON " + strTableName + " (";
 for (int i = 0; i < arrFields.GetLength(0); i++)
 {
  if (arrFields[i, 0]  null)
   break;
  if (arrFields[i, 0] != null)
  {
   strQuery += arrFields[i, 0].ToString().Trim() + ", ";
  }
 }
 strQuery = strQuery.Substring(0, strQuery.Length - 2);
 strQuery += ") " +
  "KEY INDEX [" + strIndexKey + "] " +
  "ON [" + strCatName + "] " +
  "WITH CHANGE_TRACKING AUTO;";
 SqlDataReader sdr = sa.ExecSP(strQuery);
 if (sa.LastQueryError != "")
 {
  strReturn = sa.LastQueryError;
 }
 return strReturn;
}

A typical example of the query that is executed is as follows:

CREATE FULLTEXT INDEX 
  ON Person.Address (AddressLine1, AddressLine2, City, PostalCode) 
  KEY INDEX [PK_Address_AddressID] 
  ON [Cat_AdventureWorks_Person_Address] 
  WITH CHANGE_TRACKING AUTO;

As you can see, a FTS index is created from a collection of FTS indexable columns, and the table's most appropriate key index is used to create the reference that unites the table and its FTS index. A FTS index is created in a FTS catalog, here Cat_AdventureWorks_Person_Address, that functions as the container in which one or more FTS indexes can live.

While the FTS catalog has a name, the FTS index doesn't. We'll see that when we refer to a FTS index during a query that searches the FTS index, we'll use the table's name instead of the FTS index name.

If no error is generated, the process of creating the FTS index is monitored using AJAX and JavaScript's setTimeOut() functionality that allows for repetitive function execution. The JavaScript code that achieves this looks like the following:

function showIndexCreationInfo() {
  window.status = "Creating index process running... getting index creation 
    info..." + countstatus;
   Anthem_InvokePageMethod('GetCatInfoNow', null, function( result ) 
   { 
     if (result.error) 
     {
      addOptionToSelect(document.getElementById("lbResults"), result.error, 
        "4");
     } 
     else
     {
       cbResult(result);
       ft = window.setTimeout("showIndexCreationInfo()", 500);
       if (blnPopulateStatusReady  1 && blnMergeStatusReady  1)
       {
         window.clearTimeout(ft);
         addOptionToSelect(document.getElementById("lbResults"), "Index was 
           created successfully", "3");
         window.status = "Creating index... done.";
         document.getElementById("btnFillTable").value = "Done!";
         document.getElementById("btnTestCatalog").disabled = false;
       }
       countstatus++;
     }
   } 
 }

Note that the JavaScript function that is called is an anonymous one, that is, it's embedded in the callback function parameter and therefore has no name.

The line where the function is called repetitively (using setTimeout()) is as follows:

ft = window.setTimeout("showIndexCreationInfo()", 500);

The clearTimeout() makes sure that the function showIndexCreationInfo() is not called anymore.

To get the information during the indexing process, a query is executed each time the function shown above is being executed. The code that actually constructs and sends the query is called showIndexCreationInfo() and its code is as follows:

public string[] GenCatStatus(string strCatName)
{
  string[] arrResult = new string[8];
  strQuery = "SELECT AccentSensitivity = FULLTEXTCATALOGPROPERTY ('" +   
    strCatName + "', 'AccentSensitivity'), " +
  "IndexSize = FULLTEXTCATALOGPROPERTY ('" + strCatName + "', 'IndexSize'), 
  "  +
  "ItemCount = FULLTEXTCATALOGPROPERTY ('" + strCatName + "', 'ItemCount'), 
  " +
  "LogSize = FULLTEXTCATALOGPROPERTY ('" + strCatName + "', 'LogSize'), " +
  "MergeStatus = FULLTEXTCATALOGPROPERTY ('" + strCatName + "',
  'MergeStatus'), " +
  "PopulateCompletionAge = FULLTEXTCATALOGPROPERTY ('" + strCatName + "', 
  'PopulateCompletionAge'), " +
  "PopulateStatus = FULLTEXTCATALOGPROPERTY ('" + strCatName + "',  
  'PopulateStatus'), " +
  "UniqueKeyCount = FULLTEXTCATALOGPROPERTY ('" + strCatName + "', 
  'UniqueKeyCount')";
  SqlDataReader sdr = sa.ExecSP(strQuery);
  int i = 0;
  while (sdr.Read())
  {
    arrResult[i] = sdr[0].ToString();
    arrResult[i+1] = sdr[1].ToString();
    arrResult[i+2] = sdr[2].ToString();
    arrResult[i+3] = sdr[3].ToString();
    arrResult[i+4] = sdr[4].ToString();
    arrResult[i+5] = sdr[5].ToString();
    arrResult[i+6] = sdr[6].ToString();
    arrResult[i+7] = sdr[7].ToString();
    i++;
  }
  return arrResult;
}

An example of the query being built from the above code snippet is as follows:

SELECT 
  AccentSensitivity = FULLTEXTCATALOGPROPERTY
    ('Cat_AdventureWorks_Person_Address', 'AccentSensitivity'), 
  IndexSize = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'IndexSize'), 
  ItemCount = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'ItemCount'), 
  LogSize = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'LogSize'), 
  MergeStatus = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'MergeStatus'), 
  PopulateCompletionAge = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'PopulateCompletionAge'), 
  PopulateStatus = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'PopulateStatus'), 
  UniqueKeyCount = FULLTEXTCATALOGPROPERTY 
    ('Cat_AdventureWorks_Person_Address', 'UniqueKeyCount')

You can see from the above query that it obtains the current values from eight different FTS index creation properties. While some may not be important in taking control on the process itself, I included them here so you can see what happens with each of them as the process proceeds.

The query returns the results that are kept in a comma-separated string. Once returned to the client (in this case, the browser) it is split back to an array of eight elements.

The JavaScript function cbResult() is called that updates various controls on the page.

function cbResult(result) {
  var res = result.value;
  if (result.error) 
  {
    addOptionToSelect(document.getElementById("lbResults"), "Index could not 
    be created - " + result.error, "3");
  } 
  else 
  {
    if (res != "") 
    {
      var resultSplitted = result.value.split(",");
      document.form1.txtAccentSensitivity.value = resultSplitted[0];
      document.form1.txtIndexSize.value = resultSplitted[1];
      document.form1.txtItemCount.value = resultSplitted[2];
      document.form1.txtLogSize.value = resultSplitted[3];
      document.form1.txtMergeStatus.value = resultSplitted[4];
      document.form1.txtPopulateCompletionAge.value = resultSplitted[5];
      document.form1.txtPopulateStatus.value = resultSplitted[6];
      document.form1.txtUniqueKeyCount.value = resultSplitted[7];
      if (resultSplitted[9] != null && resultSplitted[9] != "0")
      {
        alert(resultSplitted[9]);
        blnIndexCreatedSuccessfully = 0;
      }
      var rawpercent = ((resultSplitted[2] / resultSplitted[8] ) * 100 ) / 2
      if (showDebug  1)
      {
        document.getElementById(‘txtPercCatalog’).value = rawpercent;
      }
      var rawpercentRounded = Math.round(rawpercent);
      if (showDebug  1)
      {
        document.getElementById('txtPercCatalogRounded').value = 
          rawpercentRounded;
      }
      document.getElementById("divProgressBar").style.backgroundColor="red";
      document.getElementById("divProgressBar").style.width = 
        (rawpercentRounded * 5) + "px";
      if (resultSplitted[6]  "0" && blnIndexCreatedSuccessfully  1)
      {
        blnPopulateStatusReady = 1;
        if (resultSplitted[4]  "0")
        {
          blnMergeStatusReady = 1;
        }
      }  
    }
    else
    {
      document.form1.txtResultCreationIndex.value =     
      document.form1.txtResultCreationIndex.value + " – " + result.value 
        + " – " + "|";
    }
  }

}

Module 3 – The FTS Index Testing Part

The last module is where you can test the FTS index that just created as seen in Figure 4.

Figure 4. The FTS Index Testing module
This figure has been reduced in size to fit in the text. To view the full image Click here

Running a SQL query

Running a SQL query that searches a FTS index does not require any code that we have not already discussed:

public SqlDataReader ExecQuery(string strQueryText)

{
 SqlDataReader sdr;
 if (strQueryText != "")
 {
  sdr = sa.ExecSP(strQueryText);
  if (sdr == null)
   _strLastSyntaxError = sa.LastQueryError;
  return sdr;
 }
 else
 {
  _strLastSyntaxError = "";
  sdr = null;
  return sdr;
 }

}

An example of a query that is executed is as follows:

SELECT * FROM Person.Address,
  CONTAINSTABLE, ‘ "New York" ‘) T1
  WHERE AddressID = T1.[Key]
  ORDER BY City ASC

The method ExecSP() opens the database and lets SQL Server execute the passed in query. The results are returned as a SqlDataReader object and then displayed using a GridView.

Drag and Drop Query Builder

To ease the creation of the queries you need to write I’ve included a simple drag and drop query builder that lets you drag T-SQL key words like SELECT and WHERE to a textbox area as seen in Figure 5:

Figure 5. Inserting SQL Keywords to build a FTS Indexing Query

Actually there’s no C# code related to this topic – it’s all done using JavaScript, as you can see in the code shown below. The first two snippets are control definitions with their events, ondragstart() is fired when the image is dragged, and ondrop() is fired when the object is actually dropped onto the text area.

<img alt="SELECT" id="oSourceSELECT" ondragstart="fnSetInfoSELECT()"
 src="img/SELECT.bmp" style="z-index: 122; left: 18px; position: absolute;
 top: 90px" />

<textarea runat="server" ondragenter="fnCancelDefault()"
 ondrop="fnGetInfoTA()" ondragover="fnCancelDefault()"               
 id="txtTextTA" style="z-index: 105; left: 8px; position: absolute; top:
 146px; width: 462px; height: 102px;"></textarea>

The JavaScript functions below simply add the text SELECT to the text area:

function fnSetInfoSELECT() {
  event.dataTransfer.setData("Text", "SELECT "); 
  event.dataTransfer.effectAllowed = "copy";                 

}

function fnGetInfoTA() {
  event.returnValue = false;                           
  event.dataTransfer.dropEffect = "copy";              
  window.document.all["txtTextTA"].value += 
    event.dataTransfer.getData("Text");

}

The drag and drop query builder also lets you add the table’s name and columns as seen in Figure 6:

Figure 6. Inserting Table name and columns

The code to insert the text is straight forward:

this.txtTextTA.Value += this.cbTableColumns.SelectedItem.Value + " ";

this.txtTextTA.Value += this.cbTables.SelectedItem.Value + " ";

The sample contains a lot more code that can be explored on your own, but I have provided an overview of the most pertinent code to get you started.

Conclusion

All in all, you’ve seen that developing a web application that creates, monitors, and runs queries on FTS indexes require many different aspects, but divided into logical modules it becomes less complex and manageable. You have also seen how you can take advantage of using the SQLDMO API in your web applications, it makes getting information about a specific SQL Server instance very easy.

Another aspect that has been used is two flavors of AJAX. For the first module, you have seen how you can use MS Atlas to ~ajaxify’ certain areas of your web page, and how easy it actually has become. In module two I have demonstrated the use of Anthem.NET, a library that comes in handy when you need more control on the client side, using JavaScript.

The third module has shown you how to query your new FTS index using T-SQL, and showed a way to enhance the tedious job of writing test queries.

You have now a tool that can help you create and maintain your collection of catalogs and indexes more easily. Enjoy it!