Create and test the full-text index of SqlServer.

Create and test the full-text index of SqlServer.

-- Create test TABLE -- drop table FullTextIndexingCREATE TABLE FullTextIndexing (id int identity (1, 1) not null, Sentence VARCHAR (MAX )) -- create a CLUSTERED index alter table FullTextIndexing add constraint PK_FullTextIndexing primary key clustered (id asc) GO -- copy all the text on the "full-text search overview" page to txt, then pour into the table FullTextIndexing -- https://msdn.microsoft.com/zh-cn/library/ms142547 (v = SQL .105 ). aspx -- repeat for 15 times, FROM 47 rows to 1.54 million rows insert into FullTextIndexing (Sentence) SELECT Sentence FROM FullTextIndexingGO 15 select count (*) FROM FullTextIndexing

-- [Note] do not execute the deleted statement!

-- Create full-text directory -- https://msdn.microsoft.com/zh-cn/LIBRARY/ms189520 (v = SQL .105 ). aspxCREATE fulltext catalog [Catalog_Test] withaccent_sensitialog = ON -- distinguish accent as default -- DEFAULT directory AUTHORIZATION [dbo]; -- full text directory owner GO -- Change Full Text directory properties -- https://msdn.microsoft.com/zh-cn/LIBRARY/ms176095 (v = SQL .105 ). aspxALTER fulltext catalog [Catalog_Test] rebuild with accent_sensitialog = ON; -- regenerate the entire directory and differentiate the accent -- REORGANIZE; -- reorganizing the full-text directory -- as default; -- specify this directory as the default directory GO -- remove the full-text directory from the database (delete the full-text index first) -- https://msdn.microsoft.com/zh-cn/LIBRARY/ms188403 (v = SQL .105 ). aspxDROP fulltext catalog [Catalog_Test]; GO -- create interference 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 and delete interfering words -- https://msdn.microsoft.com/zh-cn/library/cc280871 (v = SQL .105 ). aspxALTER fulltext stoplist [Stoplist_Test] add n 'to 'language 2052; go alter fulltext stoplist [Stoplist_Test] drop n' to 'language 2052; -- all language 'English '-- ALLGO -- remove the full text non-indexed word table from the 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) -- specifies the INDEX column and specifies the LANGUAGE stored in the column to facilitate filtering key index PK_FullTextIndexing -- full text KEY: the unique index name in the current table ON [Catalog_Test] -- specifies the full-text directory WITH (STOPLIST [Stoplist_Test], -- specifies the full-text non-index Word Table CHANGE_TRACKING AUTO -- AUTO-fill ); GO -- Change Full Text Index properties -- https://msdn.microsoft.com/zh-cn/library/ms188359 (v = SQL .105 ). aspx -- activate full-text index alter fulltext index on [dbo]. [FullTextIndexing] ENABLE; GO -- remove full text index -- https://msdn.microsoft.com/zh-cn/library/ms184393 (v = SQL .105 ). aspxDROP fulltext index on [dbo]. [FullTextIndexing]; GO

-- Test the common query method (first query all data, put it in the memory: 1.54 million rows 31 seconds) SELECT * FROM FullTextIndexingSET statistics io onset statistics time onselect * FROM FullTextIndexing WHERE Sentence LIKE '% full-text index %'/* several times, which takes 13440 milliseconds for SQL Server analysis and compilation: CPU time = 0 ms, occupied time = 0 ms. (229376 rows affected) Table 'fulltextindexing '. Scan count 1, logical reads 15633, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. SQL Server execution time: CPU time = 11591 ms, occupied time = 13440 Ms. */SELECT * FROM FullTextIndexing where charindex ('full-text Index', Sentence) <> 0/* is executed several times, which takes 15338 milliseconds for SQL Server analysis and compilation: CPU time = 0 ms, occupied time = 0 ms. (229376 rows affected) Table 'fulltextindexing '. Scan count 1, logical reads 15633, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. SQL Server execution time: CPU time = 12714 ms, occupied time = 15338 Ms. */

-- Full-text index method: SELECT * FROM [dbo]. [FullTextIndexing] where freetext (Sentence, 'full-text Index');/* executes several times and takes 17402 milliseconds for SQL Server analysis and compilation: CPU time = 16 milliseconds, time used = 21 Ms. (851968 rows affected) Table 'fulltextindexing '. Scan count 1, logical reads 15633, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. SQL Server execution time: CPU time = 2230 ms, occupied time = 17402 Ms. */

-- It took 17 seconds to execute the command, but it went up without downgrading !!~

-- Regenerate the full-text directory !~ Re-Execute (more methods for reference: use full-text search to query SQL Server) -- 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 Index'); SELECT * FROM [dbo]. [FullTextIndexing] where contains (Sentence, 'full-text Index'); SELECT * FROM [dbo]. [FullTextIndexing] where contains (Sentence, 'full text AND Index');/* This is much faster! ~ Query completed in less than 1 second !~ However, the number of rows returned is only over 1000. SQL Server Analysis and Compilation Time: CPU time = 0 milliseconds, and time used = 0 milliseconds. (8853 rows affected) Table 'fulltextindexing '. Scan count 0, logical read 27121, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. SQL Server execution time: CPU time = 78 ms, occupied time = 944 Ms. */SET STATISTICS IO OFFSET STATISTICS TIME OFF

The rule will be found after continuous execution: the number of table scans is 0. More and more logical reads, more and more time-consuming, more and more rows are returned for queries, and the performance is getting worse !~ It is worse than "like" and has to be re-organized (REBUILD/REORGANIZE) full-text index directory logical reads 27121 logical reads 945268 logical reads 1212885 logical reads 1407846 logical reads 1736686 logical reads 1953265 logical reads

-- Query the sentence splitting result. select * from sys. dm_fts_parser ('full-text Index', 2052,5, 0) -- if you only need the full-text key or ranking information, you can use the table value function. You can use the join prompt or query prompt (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 view: select * from sys. syslanguagesselect * from sys. fulltext_indexesselect * from sys. fulltext_catalogs where name = 'catalog _ test' select * from sys. dm_fts_active_catalogs where name = 'catalog _ test' select * from sys. fulltext_stoplists where name = 'stoplist _ test' select * from sys. fulltext_stopwords where stoplist_id = 5 -- and must age_id = 2052 select * from sys. dm_fts_parser ('full-text Index', 2052,5, 0)