rime.sql

-- With the Rime of the Ancient Mariner
USE master
GO

SET NOCOUNT ON
GO

DBCC TRACEON(610)	-- Minimal logging
GO

-- ALTER DATABASE fullTextDemo4 SET MULTI_USER
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo4' )
BEGIN
	ALTER DATABASE fullTextDemo4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE fullTextDemo4
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo4' )
CREATE DATABASE fullTextDemo4
GO

ALTER DATABASE fullTextDemo4 SET RECOVERY SIMPLE
GO

USE fullTextDemo4
GO


CREATE TABLE dbo.documentText (
    dtId			INT IDENTITY,
	documentId		INT NOT NULL,
	documentText	VARCHAR(MAX),
	
	CONSTRAINT PK_documentText PRIMARY KEY CLUSTERED 
	(
		dtId ASC
	)
)
GO

-- Load up the file
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp (
	documentText	VARCHAR(MAX),
)
GO

BULK INSERT #tmp
FROM 'D:\temp\THE RIME OF THE ANCIENT MARINER.txt'


INSERT dbo.documentText ( documentId, documentText )
SELECT 1, documentText 
FROM #tmp
WHERE documentText != 'END OF FILE'
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List4' ) 
CREATE FULLTEXT CATALOG ftc_List4
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.documentText ( documentText ) KEY INDEX PK_documentText ON ftc_List4 
WITH CHANGE_TRACKING MANUAL
GO

--ALTER FULLTEXT INDEX ON dbo.Users SET STOPLIST = OFF

ALTER FULLTEXT INDEX ON dbo.documentText START FULL POPULATION;
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

		--SELECT outstanding_batch_count, *
		--FROM sys.dm_fts_index_population
		--WHERE database_id = DB_ID()

		--SELECT *
		--FROM sys.dm_fts_outstanding_batches
		--WHERE database_id = DB_ID()

	WAITFOR DELAY '00:00:01'

	SET @i += 1
	IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO


-- Write a query against the column
SELECT *
FROM dbo.documentText
WHERE documentText Like '%albatross%'

-- Have a look at the column statistics
DBCC SHOW_STATISTICS ( 'dbo.documentText', 'documentText' )

-- Estimated number of rows is 812, and it's a full clustered index scan because of the wildcards.
SELECT * 
FROM dbo.documentText
WHERE CONTAINS ( documentText, 'albatross' )
GO

SELECT * 
FROM dbo.documentText
WHERE FREETEXT ( documentText, 'albatross' )
GO


SELECT * 
FROM dbo.documentText
WHERE FREETEXT ( documentText, 'ship' )
EXCEPT
SELECT *
FROM dbo.documentText
WHERE CONTAINS ( documentText, 'ship' )



SELECT *
FROM sys.dm_fts_index_keywords( DB_ID(), OBJECT_ID('dbo.documentText') )


SELECT *
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )


SELECT TOP 10 display_term, COUNT(*)
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )
GROUP BY display_term
ORDER BY 2 DESC



SELECT *
FROM sys.dm_fts_index_keywords_position_by_document( DB_ID(), OBJECT_ID('dbo.documentText') )