Full-text index Learning

Create table products
(
Pid int primary key,
[No] varchar (100) unique not null,
Pname varchiar (100 ),
Shijian timestamp
)
Insert products select 1, 'pro1', 'zhang san'
Insert products select 2, 'pro2', 'Li si'
Insert products select 3, 'pro3', 'wang wu'
Insert products select 4, 'pro4', 'zhao liu'
Insert products select 5, 'pro5', 'chu 7'
Insert products select 6, 'pro6', 'Kang xinni'
Insert products select 7, 'pro7', 'bra'
Insert products select 8, 'pro8', 'underwear'
Insert products select 9, 'pro9', '2017 underwear'
Insert products select 10, 'pro10', 'wqa'
Insert products select 11, 'pro11', 'China AA'
Insert products (pid, [no], pname) select 11189, 'proval', 'plum'
Insert products (pid, [no], pname) select 11190, 'pro1334', 'Li'
Insert products (pid, [no], pname) select 11195, 'pro133336 ', 'gang'
Insert products (pid, [no], pname) select 11196, 'pro133337', 'tea'
Insert products (pid, [no], pname) select 111907, 'pro133338', 'Lili'

Declare @ I int
Set @ I = 1
While (@ I <100000)
Begin
Set @ I = @ I + 1
Insert products select @ I, 'P' + cast (@ I as varchar (10), 'bb' + cast (@ I as varchar (10), null
End

Select * from products
Delete products where pid <180000
Drop table products

-- Enable full-text index support for Databases
Execute sp_fulltext_database 'enable'
Go
-- Create the full-text directory ft_titles
Execute sp_fulltext_catalog 'myfulltext', 'create', 'e: \ fulltext' -- full-text directory name, operation method, path
Go

-- Create full-text index data element for the products table. UPKCL_titleidind is the unique index created by the primary key, which can be known by sp_help products.
Execute sp_fulltext_table 'products', 'create', 'myfulltext', 'pk _ products _ 1367E606 '-- table name, operation method, full-text directory name, unique key name (or primary key)
Go
-- Execute sp_fulltext_table 'products', 'drop' Delete full-text index table tags

-- Set full-text index column name
Exec sp_fulltext_column 'products', 'No', 'add'
Go
Exec sp_fulltext_column 'products', 'pname', 'add'
Go

-- Create full-text index
Exec sp_fulltext_table 'products', 'activate' -- activate the table, that is, register the table to the full-text directory in the file system.
Exec sp_fulltext_table 'products', 'start _ full' -- use full table Filling
Exec sp_fulltext_table 'products', 'start _ incremental '-- incremental table Filling
-- Enable change tracking. If you enable change tracking after adding data to a table, the full-text index is automatically filled.
Exec sp_fulltext_table 'products', 'start _ change_tracking '-- start incremental filling of full-text indexes. If the table does not have a timestamp (in fact, the table does not have a timestamp column), the full-text index is fully filled. Start to track table changes.
Exec sp_fulltext_table 'products', 'start _ background_updateindex'

-- The following two statements stop the change tracking:
Exec sp_fulltext_table 'products', 'Stop _ change_tracking'
Exec sp_fulltext_table 'products', 'Stop _ background_updateindex'
Go

-- Fill in the full-text INDEX DIRECTORY
-- Use full filling
Exec sp_fulltext_catalog 'myfulltext', 'start _ full' -- use full directory Filling
Go
-- Use incremental filling. The condition is that a timestamp Column exists in the full-text index table.
Exec sp_fulltext_catalog 'myfulltext', 'start _ incremental'
-- Delete an existing full-text directory from the file system, recreate the full-text directory, and associate the full-text directory with all tables with full-text index reference. That is, to delete the full-text index
Exec sp_fulltext_catalog 'myfulltext', 'rebuilt'
Go

-- Query the full-text index directory filling status
Declare @ t int
Select fulltextcatalogproperty ('myfulltext', 'populatestatus ')

Select fulltextcatalogproperty ('myfulltext', 'populatecompletionage ')

Select * from products
Where contains (pname, 'A ')

 

 

 

 

 

 

 

 

Declare @ beginDate DateTime, @ endDate DateTime
Set @ beginDate = GETDATE ()
Declare @ I int
While (1 = 1)
Begin
Select @ I = fulltextcatalogproperty ('myfulltext', 'populatestatus ')
If (@ I = 0)
Begin
Set @ endDate = GETDATE ()
Break
End
End
Print DATEDIFF (mm, @ beginDate, @ endDate)
Print DATEDIFF (ss, @ beginDate, @ endDate)

-- Incremental filling takes about 13 seconds
-- Full filling takes about 28 seconds
Select fulltextcatalogproperty ('myfulltext', 'populatestatus ')

Select * from products
Where contains (pname, '62 ')

Select * from products
Where pname like '% 100'