Scenario A: Enabling Others to Issue Full-text Queries

This scenario demonstrates how to use SQL Server Service Manager to enable others to issue full-text queries against the writers and books tables. This example assumes that some tables have been added to the pubs database.

1. Use SQL Server Service Manager to verify that the full-text service, Microsoft Search, is running.

   If necessary, the service can be started and stopped in one of these ways:

   • Use the context menu of the Full-Text Search service in SQL Server Enterprise Manager.
   • Use Microsoft Search service in SQL Server Service Manager.
   • Type net start mssearch from a command prompt.
2. Find out if the pubs database has been enabled for full-text processing by executing this statement:

   SELECT DatabaseProperty ('Pubs',  'IsFulltextEnabled' )

     

   This returns 1 if full-text support has been enabled, and 0 if it has not.

3. If not enabled (which is the default for newly created databases), enable the pubs database for full-text processing. Using pubs, execute this stored procedure:

   sp_fulltext_database  'enable'

     

4. Create a full-text catalog named PubsCatalog, opting for the default directory, by executing this stored procedure:

   sp_fulltext_catalog 'PubsCatalog', 'create'   

     

   This creates metadata about a full-text catalog in the database’s system tables and builds an empty full-text catalog in the file system.

5. Register the writers and books tables for full-text processing by executing this stored procedure once for each table:

   sp_fulltext_table  'writers',  'create',  'PubsCatalog',

   'writer_id_index'

   sp_fulltext_table  'books',  'create',  'PubsCatalog', 'isbn_index'

     

   Both tables use the PubsCatalog full-text catalog. These calls create metadata about both full-text indexes.

6. For each table, specify the names of the columns that are to support full-text queries by executing this stored procedure once for each column:

   sp_fulltext_column  'writers',  'organization',  'add'

   sp_fulltext_column  'writers',  'bio',  'add'

   sp_fulltext_column  'books',  'writer_name',  'add'  

   sp_fulltext_column  'books',  'the words',  'add'

   sp_fulltext_column  'books',  'abstract',  'add'

     

Note A mistake was made for the sake of illustration: for the books table, the writer_name column, rather than the titles column, has been registered.

These calls augment metadata about both full-text indexes.

7. Create a full-text index for these tables by executing this stored procedure once for each table:

   sp_fulltext_table  'writers',  'activate'

   sp_fulltext_table  'books',  'activate'

     

   This does not actually create the full-text indexes. Rather, it registers the tables in the full-text catalog so that data from these tables will be included in the next population.

8. Start a full population of the PubsCatalog full-text catalog by executing this stored procedure:

   sp_fulltext_catalog  'PubsCatalog',  'start_full'

     

   Because the population of a full-text catalog is an asynchronous operation, it is unlikely that the full-text indexes are created immediately.

9. Verify the progress of the population of the PubsCatalog full-text catalog by executing this statement:

   SELECT FulltextCatalogProperty ( 'PubsCatalog',  'PopulateStatus')

     

   This returns 0 if the service is idle for the full-text catalog and therefore finished, and 1 or more to indicate various stages of population.

10. Issue Transact-SQL queries to confirm that the administration was executed correctly. For example:

    SELECT B.writer_name, B.pub_date, B.the_words, A.royalties_ytd

    FROM writers A,  books B

    WHERE A.writer_name = B.writer_name

    AND A.citizenship = 'Canadian'

    AND CONTAINS (B.the_words, '"Indexing Service" NEAR "Indexing Service"')

      

    SELECT writer_name, pub_date, abstract

    FROM books 

    WHERE CONTAINS (title, '"Classic" NEAR "French" NEAR "Cooking"')

      

    This last query results in an error because the title column was not enabled for full-text queries.

11. Check for errors by executing this statement:

    SELECT ColumnProperty ( ObjectId('books'), 'title',  'IsFullTextIndexed' )

      

    This returns 1 if the title column is part of the full-text index for the books table, and 0 if it is not.

12. List the columns that participate in full-text processing for the books table by executing this stored procedure:

    sp_help_fulltext_columns  'books'

      

Note The results of this query show that there was a mistake and that the writer_name column, rather than the title column, was included in the full-text index definition.

13. Deactivate the books table so that the title column can be added to the full-text index and the writer_name column can be removed by executing this stored procedure:

    sp_fulltext_table  'books',  'deactivate'

      

    In addition to allowing columns to be added and deleted, deactivating the books table means that the table no longer participates in the population of the PubsCatalog full-text catalog. However, the metadata remains and the table can be reactivated. The existing full-text index for the books table remains in place until the next full population of the PubsCatalog full-text catalog, but it is unused because Microsoft® SQL Server™ blocks queries on deactivated tables.

14. Add the title column and remove the writer_name column from the metadata for the full-text index of the books table. Execute this stored procedure once for each column:

    sp_fulltext_column  'books',  'writer_name',  'drop'  

    sp_fulltext_column  'books',  'title',  'add'

      

15. Reactivate the books table using this stored procedure:

    sp_fulltext_table  'books',  'activate'

      

    If the table is reactivated and the index is not repopulated, the old index is still available for queries against the full-text enabled columns that remain, but not for queries against any new full-text enabled columns. Before repopulation, data from deleted columns can be matched on queries that specify a search of all full-text columns by typing an asterisk (*) for the column name.

16. Start an incremental population of the PubsCatalog full-text catalog by executing this stored procedure:

    sp_fulltext_catalog  'PubsCatalog',  'start_incremental'

      

    An incremental population refreshes the full-text catalog by indexing data in full-text enabled columns with these characteristics:

    • Rows that have been updated or inserted since the last population.
    • Tables that have a timestamp column.
    • All rows that have been enabled for full-text processing since the last population, or that have a schema that has been modified in any way since the last population.
17. After repopulation of the PubsCatalog full-text catalog completes, reissue the Transact-SQL query from Step 10. This time, no error occurs.