Example of Combining Full-text Administration and Full-text Query

Full-text indexes can be administered using either SQL Server Enterprise Manager or stored procedures. Sometimes it is convenient to combine full-text administrative stored procedures in the same script as the queries. The following example script combines these tasks:

USE pubs

-- Create and populate a table.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'FulltextTest')

    DROP TABLE FulltextTest

GO

CREATE TABLE FulltextTest

             ( article_id int IDENTITY(100,1)

                             CONSTRAINT PK_title_id PRIMARY KEY,

               article_title nvarchar(200)

             )

GO

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan has always enjoyed ice skating.')

INSERT FulltextTest (article_title) VALUES (N'Elvis Stoiko: The best male figure skater')

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan On Ice: Skating Reaches Tops in Public Opinion Poll')

INSERT FulltextTest (article_title) VALUES (N'Last night, Steven Buchanan skated on the ice!! Skating fans cheer!')

INSERT FulltextTest (article_title) VALUES (N'Ice-skating brings out the best in Steven. Buchanan exults in first victory...')

GO

  

-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'

GO

  

-- Create a new full-text catalog.

EXEC sp_fulltext_catalog 'StevenBCatalog',

                         'create'

GO

  

-- Register the new table and column within it for full-text querying,

-- then activate the table.

EXEC sp_fulltext_table 'FulltextTest',

                       'create',

                       'StevenBCatalog',

                       'PK_title_id'

EXEC sp_fulltext_column 'FulltextTest',

                        'article_title',

                        'add'

EXEC sp_fulltext_table 'FulltextTest',

                       'activate'

GO

  

-- Start full population of the full-text catalog. Note that it is

-- asynchronous, so delay must be built in if populating a

-- large index.

  

EXEC sp_fulltext_catalog 'StevenBCatalog',

                         'start_full'

WHILE (SELECT fulltextcatalogproperty('StevenBCatalog',

'populatestatus')) <> 0

   BEGIN

      WAITFOR DELAY '00:00:02'     -- Check

      every 2 seconds to see if full-text index population is complete.

   CONTINUE

END

  

GO

  

-- Execute a full-text query against the new table.

SELECT article_title

FROM FulltextTest

WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')

  

Here is the result set:

article_title                                            

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

Steven Buchanan has always enjoyed ice skating.

Last night, Steven Buchanan skated on the ice!! Skating fans cheer!

Steven Buchanan On Ice: Skating Reaches Tops in Public Opinion Poll

Ice-skating brings out the best in Steven. Buchanan exults in first victory...

(4 row(s) affected)

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.