An example of a full Full-text index for a SQL Server database

Source: Internet
Author: User
Keywords Network programming MSSQL tutorials
Tags activate catalog create example full-text search function index name

An example of a full-text index for SQL Server databases, in the pubs database.


first, the specific steps to create a FULL-TEXT index using system stored procedures are described:


1) to start the Full-text processing function of the database (sp_fulltext_database)


2) To establish a Full-text catalog (sp_fulltext_catalog)


3 Registers a table (sp_fulltext_table) that requires Full-text indexing in a Full-text catalog


4 indicates the column names (sp_fulltext_column) that require full-text indexing in the table


5) To create a Full-text index (sp_fulltext_table) for a table


6) Populate Full-text catalogs (sp_fulltext_catalog)


---------******** Sample ********-------------


to establish a Full-text index of the title and notes columns of the pubs database, and then use the index query Title column or a notes column that contains a datebase or computer string in the book name:


before this, you need to install the Microsoft Search service to start the SQL Server Full-text Search service


user Pubs--Open database


Go


-Check to see if the database pubs support Full-text indexing if
is not supported

-Opens the feature using Sp_fulltext_database


if (select Databaseproperty (' pubs ', ' isfulltextenabled ')) =0


execute sp_fulltext_database ' Enable '


--Establish Full-text catalog Ft_pubs


execute sp_fulltext_catalog ' ft_pubs ', ' Create '


--Establish Full-text indexing data for the title table


execute sp_fulltext_table ' title ', ' Create ', ' ft_pubs ', ' Upkcl_titleidind '


--Set full-text indexed column names


execute sp_fulltext_column ' title ', ' title ', ' Add '


execute sp_fulltext_column ' title ', ' Notes ', ' Add '


--Establishing Full-text indexing


--activate is the ability to activate the Full-text retrieval of a table, which is to register the table in a Full-text catalog


execute sp_fulltext_table ' title ', ' Activate '


--Populate the Full-text indexing directory


execute sp_fulltext_catalog ' ft_pubs ', ' start_full '


Go


--Check full-text catalog padding


while FullTextCatalogProperty (' ft_pubs ', ' populatestatus ') <>0


begin


--If the Full-text catalog is in a filled state, wait 30 seconds before checking again


waitfor DELAY ' 0:0:30 '


End


-When Full-text catalog population is complete, you can use Full-text catalogs to retrieve


Select Title


form


where CONTAINS (title, ' database ')


or CONTAINS (title, ' computer ')


or CONTAINS (notes, ' database ')


or CONTAINS (notes, ' database ')


 


'--------------The following describes the system stored procedures for Full-text operations


process Name: Sp_fulltext_service


Execute permissions: serveradmin or system administrator


function: Set Full-text search Properties


process Name: sp_fulltext_catalog


Execute permissions: db_owner and higher role members


function: Create and delete a Full-text catalog, start or stop indexing operations for a Full-text catalog


process Name: Sp_fulltext_database


Execute permission: db_owner role member


function: Initialize full-text indexing or delete all Full-text catalogs in a database


process Name: sp_fulltext_table


Execute permission: db_ddladnmin or db_owner role member


role: Identifies a table as a full-text indexed table or a non-full-text indexed table


process Name: Sp_fulltext_column


Execute permission: db_ddladnmin role member


function: Indicate those columns in a full-text indexed table if or exit Full-text indexing

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.