SQL Server full-text index creation and testing

--Create a Test Table--drop table fulltextindexingcreate table fulltextindexing (ID INT IDENTITY () not null,sentence VARCHAR (MAX))- -Create a clustered index alter TABLE fulltextindexing add CONSTRAINT pk_fulltextindexing PRIMARY KEY CLUSTERED (ID ASC) go--will "overview of full-Text search" The text of the page is all copied to TXT and then poured into the table fulltextindexing--https://msdn.microsoft.com/zh-cn/library/ms142547 (v=sql.105). aspx--repeats 15 times, from 47 rows to 1.54 million rows insert into fulltextindexing (sentence) SELECT sentence from Fulltextindexinggo 15SELECT COUNT (* ) from fulltextindexing

--"Note" Delete the statement do not execute!

--Create full-text catalog--https://msdn.microsoft.com/zh-cn/library/ms189520 (v=sql.105). aspxcreate fulltext Catalog [Catalog_Test] withaccent_sensitivity = on--Accent-sensitive as default--default directory authorization [dbo];--full-text catalog owner go--change properties of full-text catalogs--https:// msdn.microsoft.com/zh-cn/library/ms176095 (v=sql.105). Aspxalter fulltext CATALOG [catalog_test] REBUILD with ACCENT_ Sensitivity = on;--Rebuild Entire directory and accent-sensitive--reorganize;--reorganize full-text Catalogs--as default;--Specify this directory as the default directory go--remove full-text catalogs from the database (full-text indexing is removed first)--https:/ /msdn.microsoft.com/zh-cn/library/ms188403 (v=sql.105). Aspxdrop fulltext CATALOG [catalog_test]; go--Create the Noise Word table--https://msdn.microsoft.com/zh-cn/library/cc280405 (v=sql.105). aspxcreate Fulltext STOPLIST [Stoplist_ Test] from SYSTEM stoplist AUTHORIZATION [dbo]; GO--Add Delete noise word--https://msdn.microsoft.com/zh-cn/library/cc280871 (v=sql.105). Aspxalter Fulltext STOPLIST [Stoplist_ Test]add N ' ' LANGUAGE 2052;   GO ALTER fulltext stoplist [Stoplist_test]drop N ' language 2052; --all LANGUAGE ' 中文版 '--allgo--Remove full-text stoplist from database--https://msdn.microsoft.com/zh-cn/library/cc280482 (v=sql.105). Aspxdrop fulltext Stoplist [stoplist_test]; GO-Create full-text index--https://msdn.microsoft.com/zh-cn/library/ms187317 (v=sql.105). Aspxcreate fulltext Index on [dbo]. [Fulltextindexing] (Sentence LANGUAGE 2052)--index column, clear column of the language stored in, easy to filter key index pk_fulltextindexing--full-text key: The current table unique index name on [catalog_test]-- Specifies that full-text catalogs with (stoplist [stoplist_test],--Specify full-text stoplist change_tracking auto--autofill); go--change the properties of the full-text index--https://msdn.microsoft.com/zh-cn/library/ms188359 (v=sql.105). aspx--Activate full-text index ALTER FULLTEXT INDEX on [ DBO]. [Fulltextindexing] ENABLE; go--Delete Full-text index--https://msdn.microsoft.com/zh-cn/library/ms184393 (v=sql.105). Aspxdrop fulltext Index on [dbo]. [Fulltextindexing]; GO

--Test the General Query method (query all data first, put into memory: 1.54 million rows 31 seconds) SELECT * from Fulltextindexingset STATISTICS IO ONSET STATISTICS time ONSELECT * from Ful ltextindexing WHERE sentence like '% full-text index% '/* executes several times and takes 13440 milliseconds SQL Server parse and compile time:    CPU time = 0 milliseconds, occupied time = 0 milliseconds. (229376 rows affected) Table ' fulltextindexing '. Scan count 1, logical read 15,633 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. SQL Server Execution time:   CPU time = 11591 milliseconds, elapsed time = 13440 milliseconds. */select * from fulltextindexing WHERE CHARINDEX (' Full-text indexing ', sentence) <>0/* executed several times and took 15338 milliseconds for SQL Server analysis and compilation time:    CPU time = 0 milliseconds, elapsed time = 0 milliseconds. (229376 rows affected) Table ' fulltextindexing '. Scan count 1, logical read 15,633 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. SQL Server Execution time:   CPU time = 12714 milliseconds, elapsed time = 15338 milliseconds. */

--the method of using full-text indexing: SELECT * FROM [dbo]. [Fulltextindexing] WHERE FREETEXT (sentence, ' Full-text indexing ');/* Executes several times and takes 17402 milliseconds for SQL Server analysis and compilation time:    CPU time = 16 milliseconds, elapsed time = 21 milliseconds. (851968 rows affected) Table ' fulltextindexing '. Scan count 1, logical read 15,633 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. SQL Server Execution time:   CPU time = 2230 milliseconds, elapsed time = 17402 milliseconds. */

--The execution of 17 seconds, not down instead of rising!! ~

--Regenerate full-text Catalogs!~ (more methods to be consulted: Querying SQL Server with full-text search)--https://msdn.microsoft.com/zh-cn/library/ms142559 (v=sql.105). Aspxalter fulltext CATALOG [catalog_test] REBUILD; Goselect * FROM [dbo]. [Fulltextindexing] WHERE FREETEXT (sentence, ' Full-text indexing '); SELECT * FROM [dbo]. [Fulltextindexing] WHERE CONTAINS (sentence, ' Full-text indexing '); SELECT * FROM [dbo]. [Fulltextindexing] WHERE CONTAINS (sentence, ' full-text and index ');/* This is a lot faster!~ the query completes in less than 1 seconds!~ but the number of rows returned is more than 1000 rows SQL Server parse and compile time:    CPU time = 0 milliseconds, elapsed time = 0 milliseconds. (8853 rows affected) Table ' fulltextindexing '. Scan count 0, logical read 27,121 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. SQL Server Execution time:   CPU time = 78 milliseconds, elapsed time = 944 milliseconds. */set STATISTICS IO OFFSET STATISTICS time OFF

Constant execution will find the pattern: The number of table scans is 0. Logical reading is also more and more, time consuming more and more, the query returns more and more rows, performance is worse than "like"!~ and finally rebuilt reorganization (rebuild/reorganize) Full-text index directory logical read 27,121 logical read 945,268 times logical read 1,212,885 logic reads 1,407,846 logic reads 1,736,686 reads logic reads 1,953,265 times

--Query sentence splitting results. You can see what words to match the query select * from Sys.dm_fts_parser (' Full text index ', 2052,5,0)--if only the full-text key or rank information is required, Table-valued functions can be used--methods that use table-valued functions can use join hints or query hints (loop/merge/hash) ALTER fulltext CATALOG [catalog_test] REBUILD; Goalter fulltext CATALOG [catalog_test] REORGANIZE; Goselect * FROM [dbo]. [fulltextindexing] T1 INNER JOIN containstable ([fulltextindexing],sentence, ' overview ') as T2on t1.id = t2. [KEY] Goselect * FROM [dbo]. [fulltextindexing] T1 INNER JOIN freetexttable ([fulltextindexing],sentence, ' Overview ', LANGUAGE 2052,1000) as T2on t1.id = t2. [KEY] ORDER by T2. RANK DESC; GO

--Related views: SELECT * FROM Sys.syslanguagesselect * from Sys.fulltext_indexesselect * from sys.fulltext_catalogs where name = ' C Atalog_test ' select * from sys.dm_fts_active_catalogs where name = ' catalog_test ' select * from Sys.fulltext_stoplists wher E name = ' stoplist_test ' select * from sys.fulltext_stopwords where stoplist_id = 5--and language_id = 2052select * from S Ys.dm_fts_parser (' Full-text index ', 2052,5,0)

SQL Server full-text index creation and testing