SQL Server full-text index

The specific implementation scheme of SQL full-text in exchange for time at the expense of space is as follows:

 

1: Create full-text in the SQL script. [assume that the loginname field (nvarchar), displayname field (nvarchar), and properties field (XML) in the sites table are used) add to the full-text file]

-- Check whether the database mydb supports full-text indexing. If not -- Use sp_fulltext_database to enable this function.  If ( Select Databaseproperty ( '  Mydb  ' , '  Isfulltextenabled  ' ) =0  Execute sp_fulltext_database  '  Enable  ' -- Create the full-text directory ft_mydb (final parameter E :/ Fulltext is a custom storage directory. If it is removed, the corresponding storage file will be created in the default place of the system.) execute sp_fulltext_catalog  '  Ft_mydb  ' , '  Create  ' , '  E:/Fulltext  ' --Create a unique index (here, you must first Delete the clustered index created by the system by default as the primary key, because only one clustered index exists in a table) create unique clustered index USERSID on DBO. users (ID) create unique clustered index sitesid on DBO. sites (ID) -- Create full-text index data element for users and sites tables execute sp_fulltext_table  '  Users  ' , '  Create  ' , '  Ft_mydb  ' , '  USERSID '  Execute sp_fulltext_table  '  Sites  ' , '  Create  ' , '  Ft_mydb  ' , '  Sitesid  ' 
-- Set the full-text index column name (loginname and displayname of users table, properties of sites table) execute sp_fulltext_column '  Users  ' , '  Loginname  ' , '  Add  '  Execute sp_fulltext_column  '  Users  ' , '  Displayname  ' ,'  Add  '  Execute sp_fulltext_column  '  Sites  ' , '  Properties  ' , '  Add  ' -- Create full-text index -- Activate: Enables full-text retrieval of a table, that is, registering the table execute sp_fulltext_table in the full-text directory. '  Users  ' , '  Activate  '  Execute sp_fulltext_table  '  Sites  ' , '  Activate  ' -- Fill in full-text index directory execute sp_fulltext_catalog  '  Ft_mydb ' , '  Start_full  '  Go -- Check full-text directory filling while fulltextcatalogproperty (  '  Ft_mydb  ' , '  Populatestatus  ' ) <> 0  Begin -- If the full-text directory is being filled, wait 30 seconds and check again waitfor Delay '    '  End 

 

2: Use the corresponding statement to filter the columns with full-text indexes. The specific statement is as follows:

 
Select * from [mydb]. [DBO]. [users] Where contains (loginname,'"Ad *"')--Obtain the select record containing ad in The nvarchar field loginname* From [mydb]. [DBO]. [sites] Where contains (properties,'"Haha"')-Obtain records containing Haha in the XML field properties.
 

 

3: Because SQL full-text cannot be consistent with the database data in real time, you must run the corresponding incremental population or full population for the full-text index in the data table, here, we can manually create a schedule mechanism to regularly execute it, so that the data in full-text is consistent with the data in the database.

However, when we look for data, we can execute an SQL statement to run the incremental population statement to ensure that the data retrieved each time is up-to-date, the SQL statement is as follows:

 
Execute sp_fulltext_catalog'Ft_my_db','Start_incremental'
 

However, the above two methods achieve data synchronization by ourselves, which will result in a loss of efficiency. We can use a better method: Let SQL Server synchronize data by itself, the SQL script is as follows:

 
Use dga_db; goalter Fulltext index on [my_db]. [DBO]. [users] Set change_tracking auto; go
 

4: For full-text indexing of SQL Server, refer to msdn for more information:

http://msdn.microsoft.com/zh-cn/library/ms142571