SQL Server 2008 Integrated FullText Search (iFTS) Demo Scripts (Part 1)

I gave a presentation and demonstration of iFTS on SQL Server 2008 CTP6 to the Denver SQL Server User Group at the Microsoft Office in Denver last Thursday, May 15, 2008.

The cleanup and creation of the fulltext catalog and indexes along with some queries is shown below. Part Two will have the SearchHistory table and the search stored procedures, while Part Three will have some useful iFTS queries for monitoring iFTS health and performance.

 

    -- Build FT Catalog and Index with AdventureWorks
    -- Glenn Berry 
    -- May 2008

    -- Switch to AdventureWorks database
    USE AdventureWorks;
    GO
    
    -- Preliminary Cleanup *******************************
    -- Disable FT Index if it exists
    IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[HumanResources].[JobCandidate]'))
    ALTER FULLTEXT INDEX ON [HumanResources].[JobCandidate] DISABLE;
    GO

    -- Delete FT Index if it exists
    IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[HumanResources].[JobCandidate]'))
    DROP FULLTEXT INDEX ON [HumanResources].[JobCandidate];
    GO
    
    -- Disable FT Index if it exists
    IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[Person].[Contact]'))
    ALTER FULLTEXT INDEX ON [Person].[Contact] DISABLE;
    GO

    -- Delete FT Index if it exists
    IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[Person].[Contact]'))
    DROP FULLTEXT INDEX ON [Person].[Contact];
    GO

    -- Delete FT Catalog if it exists
    IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'ftCatalog')
    DROP FULLTEXT CATALOG [ftCatalog];
    GO

    -- Empty SearchHistory table
    TRUNCATE TABLE dbo.SearchHistory;
    -- End of Preliminary Cleanup *******************************


    -- Create Catalogs and Indexes
    -- Create FT Catalog and set it as the default catalog with accent sensitivity on
    -- (Shows up under Storage in Object Explorer)
    CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;


    -- Get information about FullText catalogs in this database
    SELECT fulltext_catalog_id, [name], is_default, is_accent_sensitivity_on, principal_id, is_importing 
    FROM sys.fulltext_catalogs;


    -- Create FT Index on Resume column of HumanResources.JobCandidate table
    CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX PK_JobCandidate_JobCandidateID;
    
    -- Number of full-text indexed items currently in the full-text catalog 
    SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'ItemCount')AS 'Item Count';
    
    -- Size of the full-text catalog in megabytes 
    SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'IndexSize')AS 'Size in MB';


    -- Try searching for something with CONTAINSTABLE
    DECLARE @SearchTerm nvarchar(50) = 'Experience'
    DECLARE @NumItems bigint = 10

    SELECT jc.JobCandidateID, jc.EmployeeID, jc.ModifiedDate, jc.Resume, KEY_TBL.RANK
    FROM HumanResources.JobCandidate AS jc 
    INNER JOIN CONTAINSTABLE (HumanResources.JobCandidate, [Resume], @SearchTerm, @NumItems)AS KEY_TBL
    ON jc.JobCandidateID = KEY_TBL.[KEY];


    -- Try calling a stored procedure instead
    EXEC dbo.GetResumeSearchResultsContainsTable 'Experience', 10
    
    EXEC dbo.GetResumeSearchResultsContainsTable 'Visual', 10
    
    EXEC dbo.GetResumeSearchResultsContainsTable 'Visual & Basic', 10
    
    -- Check the Search History
    SELECT * 
    FROM dbo.SearchHistory
    ORDER BY SearchDateTime DESC;


    -- Create FT Index on AdditionalContactInfo column of Person.Contact table
    CREATE FULLTEXT INDEX ON Person.Contact(AdditionalContactInfo) KEY INDEX PK_Contact_ContactID;
    
    -- Set Change Tracking to Manual
    ALTER FULLTEXT INDEX ON Person.Contact SET CHANGE_TRACKING MANUAL;
    
    -- Try searching for something with CONTAINS
    DECLARE @SearchTerm nvarchar(50) = 'Call'
    DECLARE @NumItems bigint = 10
    
    SELECT TOP (@NumItems) ContactID, [Title], FirstName, LastName, AdditionalContactInfo
    FROM Person.Contact 
    WHERE CONTAINS([AdditionalContactInfo], @SearchTerm);

    -- Try calling a stored procedure instead
    EXEC dbo.GetContactInfoSearchResultsContains 'Phone', 10
    
    EXEC dbo.GetContactInfoSearchResultsContains 'Call', 10


    -- Check the Search History
    SELECT * 
    FROM dbo.SearchHistory
    ORDER BY SearchDateTime DESC;
    
    -- Generate some changes to Person.Contact (this will mess up the data by inserting the same data into multiple records)
    UPDATE TOP(50) Person.Contact
    SET AdditionalContactInfo = 
    (SELECT AdditionalContactInfo
    FROM Person.Contact
    WHERE ContactID = 1)
    WHERE AdditionalContactInfo IS NULL
    
    
    -- Find out how many changes are pending
    SELECT OBJECTPROPERTY(OBJECT_ID('Person.Contact'), 'TableFulltextPendingChanges') AS 'Full Text Pending Changes';

    -- Start a Manual Update of the FullText Catalog
    ALTER FULLTEXT INDEX ON Person.Contact START UPDATE POPULATION;



    -- Rebuild FT Catalog completely (may be very time consuming on a large catalog)
    ALTER FULLTEXT CATALOG ftCatalog 
    REBUILD WITH ACCENT_SENSITIVITY=OFF;
    

    -- Drop the FT Catalog
    DROP FULLTEXT CATALOG ftCatalog;
 
Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s