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:

• Create and populate a table.
  
  
• Enable the pubs database for full-text searching.
  
  
• Create a full-text catalog.
  
  
• Register the new table and certain columns in it for full-text search.
  
  
• Populate the new full-text catalog with full-text index information from the new table.
  
  
• Execute a full-text query against the new table.

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)