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

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.

Part Two of the demo scripts (below) has the SearchHistory table and the search stored procedures, while Part Three will have some useful iFTS queries for monitoring iFTS health and performance.

 

Note: These stored procedures won’t compile until and unless you have the fulltext index in place on the relevant tables, (see Part 1 of this series).

-- FT Related Table and Stored Procedures
-- Glenn Berry May 2008

USE [AdventureWorks]
GO

-- SearchHistory table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchHistory]') AND type in (N'U'))
DROP TABLE [dbo].[SearchHistory]
GO

CREATE TABLE [dbo].[SearchHistory](
    [SearchHistoryID] [int] IDENTITY(1,1) NOT NULL,
    [SearchTerm] [varchar](50) NULL,
    [NumItemsRequested] [int] NULL,
    [NumItemsReturned] [int] NULL,
    [SearchElapsedTime] [int] NULL,
    [SearchDateTime] [smalldatetime] NOT NULL,
    [SearchSPName] [varchar](50) NULL,
 CONSTRAINT [PK_SearchHistory] PRIMARY KEY CLUSTERED 
(
    [SearchHistoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddSearchHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddSearchHistory]
GO

/* AddSearchHistory ================================================================================
Description : Add row to SearchHistory table
                        
Author: Glenn Berry    
Date: 5/13/2008            
Input:                            
Output:    
Used By: xxx.dll                         

Last Modified   Developer        Description
-----------------------------------------------------------------------------------------------------------
5/13/2008        Glenn Berry        Created
=========================================================================================================*/
CREATE PROCEDURE [dbo].[AddSearchHistory]
(
    @SearchTerm    varchar(50), 
    @NumItemsRequested    int,
    @NumItemsReturned    int,
    @SearchElapsedTime    int,
    @SearchSPName varchar(50) = "Unknown" 
)

AS

    SET NOCOUNT ON;

     
    INSERT INTO dbo.SearchHistory
        (SearchTerm, NumItemsRequested, NumItemsReturned, SearchElapsedTime, SearchDateTime, SearchSPName) 
    VALUES 
        (@SearchTerm, @NumItemsRequested, @NumItemsReturned, @SearchElapsedTime, GETDATE(), @SearchSPName) 

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetContactInfoSearchResultsContains]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetContactInfoSearchResultsContains]
GO


/* GetContactInfoSearchResultsContains ===================================================================
Description : Return search results using CONTAINS  
                                    
Author: Glenn Berry          
Date: 5/13/2008             
Input:                                    
Output:     
Used By: xxx.dll                          

Last Modified           Developer         Description
-----------------------------------------------------------------------------------------------------------
5/13/2008                Glenn Berry       Created
=========================================================================================================*/
CREATE PROCEDURE [dbo].[GetContactInfoSearchResultsContains]
(
    @SearchTerm nvarchar(50),
    @NumItems bigint
)
AS

    SET NOCOUNT ON;

    DECLARE @StartTime datetime = GETDATE()
    
    -- Actually do the search
    SELECT TOP (@NumItems) [Title], ContactID, FirstName, LastName, AdditionalContactInfo
    FROM Person.Contact 
    WHERE CONTAINS([AdditionalContactInfo], @SearchTerm)

    -- Get the number of results and elapsed time
    DECLARE @NumberOfResults int = @@ROWCOUNT
    DECLARE @EndTime datetime = GETDATE()
    DECLARE @ElapsedTime int = DATEDIFF(ms, @StartTime, @EndTime)
    
    -- Log the results
    EXEC dbo.AddSearchHistory @SearchTerm, @NumItems, @NumberOfResults, @ElapsedTime, 'GetContactInfoSearchResultsContains'

    RETURN 


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetResumeSearchResultsContainsTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetResumeSearchResultsContainsTable]
GO


/* GetResumeSearchResultsContainsTable ===================================================================
Description : Return search results using CONTAINSTABLE  
                                    
Author: Glenn Berry          
Date: 5/13/2008             
Input:                                    
Output:     
Used By: xxx.dll                          

Last Modified           Developer         Description
-----------------------------------------------------------------------------------------------------------
5/13/2008                Glenn Berry       Created
=========================================================================================================*/
CREATE PROCEDURE [dbo].[GetResumeSearchResultsContainsTable]
(
    @SearchTerm nvarchar(50),
    @NumItems bigint
)
AS

    SET NOCOUNT ON;

    DECLARE @StartTime datetime = GETDATE()
    
    -- Actually do the search
    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]

    -- Get the number of results and elapsed time
    DECLARE @NumberOfResults int = @@ROWCOUNT
    DECLARE @EndTime datetime = GETDATE()
    DECLARE @ElapsedTime int = DATEDIFF(ms, @StartTime, @EndTime)
    
    -- Log the results
    EXEC dbo.AddSearchHistory @SearchTerm, @NumItems, @NumberOfResults, @ElapsedTime, 'GetResumeSearchResultsContainsTable'

    RETURN 


GO


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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s