-- 555555555555555555555555555555555555555

-- 1 - Create the Full Text Catalog
USE [AdventureWorks]
GO
CREATE FULLTEXT CATALOG [zzProduction.Product]
IN PATH N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]
GO

-- 2 - Create the Full Text Index
USE [AdventureWorks]
GO
CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON [zzProduction.Product] WITH CHANGE_TRACKING AUTO
GO

USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name])
GO

USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE
GO

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Start Optimize Catalog Population on AdventureWorks.zzProduction.Product', 
@enabled=1, 
@start_step_id=1, 
@description=N'Scheduled full-text optimize catalog population for full-text catalog zzProduction.Product in database AdventureWorks. This job was created by the Full-Text Catalog Scheduling dialog or Full-Text Indexing Wizard.', 
@category_name=N'Full-Text', @job_id = @jobId OUTPUT
select @jobId
GO

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Start Optimize Catalog Population on AdventureWorks.zzProduction.Product', @server_name = N'JTKLAPTOP'
GO

USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Start Optimize Catalog Population on AdventureWorks.zzProduction.Product', @name=N'zzProduction.Product Full Text Catalog ', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=1, 
@active_start_date=20070924, 
@active_end_date=99991231, 
@active_start_time=20000, 
@active_end_time=235959, 
 @schedule_id = @schedule_id OUTPUT

select @schedule_id
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Start Optimize Catalog Population on AdventureWorks.zzProduction.Product', @step_name=N'Full-Text Indexing', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=-1, 
@on_fail_action=2, 
@on_fail_step_id=-1, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0,
 @subsystem=N'TSQL', 
@command=N'USE [AdventureWorks]
ALTER FULLTEXT CATALOG [zzProduction.Product] REORGANIZE', 
@database_name=N'master'
GO

-- 3a - Show the Full Text Catalog in SSMS

-- 3b - Add the column
-- Production.ProductDescription -- Description column

-- 4 - Simple queries
-- 4a - Product ID and Product Name Selection 
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"*washer*" OR "*ball*"');
GO
 
-- 4b - Description Selection 
USE AdventureWorks;
GO
SELECT ProductDescriptionID, Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, '"*technology*" OR "*performance*"');
GO

-- 5 - OR Logic
USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE CONTAINS(*, '"*nut*" OR "*screw*" OR "*washer*"');
GO

-- 6 - AND Logic
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"flat" AND "washer"');
GO 

-- Compare to above
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"flat washer"');
GO

-- AND NOT Logic
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"nut" AND NOT "hex"');
GO

-- 7 - Prefix
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"chain*"');
GO

-- 8 - Proximity

-- 2 terms
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'men NEAR shorts');
GO 

-- 3 terms
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'XL NEAR men NEAR shorts');
GO

-- 9 - Inflection and Thesaurus
-- INFLECTIONAL
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, shift) ');
GO 

-- 10 - Weight
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'ISABOUT (nut weight (.8), 
bolt weight (.4), washer weight (.2) )' );
GO

-- 11 - Variables
-- Example 1
USE AdventureWorks;
GO
DECLARE @Parm1 varchar(50)
SET @Parm1 = 'XL NEAR men NEAR shorts'
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], @Parm1);
GO 
-- Example 2
USE AdventureWorks;
GO
DECLARE @Parm1 varchar(50)
SET @Parm1 = '"XL" OR "men" OR "shorts"'
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], @Parm1);
GO
