SQL Server full-text search and query

Solution Overview:
1. Change the file name during file storage
2. Configure the Indexing Server and associate the Indexing Server with the ms SQL Server.
3. Modify the SQL statement and add the content of the full-text query statement to the query condition.
File storage method:
To facilitate storage and indexing, we store the uploaded files in a directory. To ensure that the uploaded files are not duplicated, we use guid as the file name, and The GUID is associated with the database records. At the same time, the file suffix also retains the original file suffix, so that the Indexing Service can recognize this document.
Configure index service
Go to Computer Management) Program (Right-click my computer> Manage) and find the Index Service)
Configure the Indexing Service Function
(1) Right-click the Index Service to bring up a menu
(2) Select New-> catalog (create a new catalog)
Set the newly added catalog
(1) enter the name of the catalog (this name needs to be associated with the database. Pay attention to the name. Here we assume that
(2) Click Browse to go to the Selection Index Service Running directory form.
(3) Select the index run directory created earlier (any place)
(4) Click OK to complete this operation
After creating a directory number, you must add a directory to the directory.
(1) Right-click the created catalog
(2) Select New-> directory (new directory)
Create INDEX DIRECTORY
(1) Click Browse to go to the directory selection page.
(2) Select the directory for full-text search
(3) confirm to complete this operation
Start Index Service after creation
(1) Start the Index Service
(2) After the Index Service is started successfully, the index information indicates that the service has been started successfully.
Associate the Index Service with the SQL database:
Execute the following stored procedure in the SQL query analyer
Exec sp_addlinkedserver DCS, -- Name of the connection server, which will be used for later Query
'Index Server ',
'Msidxs ',
'Dcci' -- Name of the newly created catalog in the Index Service
Method 1
(1) Right-click the Index Service
(2) select all tasks-> tune perrformance
(3) Select User-Defined
(4) Click customize to enter the performance adjustment form.
(5) Move the attributes of index and querying to the rightmost.
(6) Click OK to complete the operation
Performance adjustment method 2
If the server does not need to index the entire system file, you can stop or delete the system catalog.
MSSQL call to Index Server
1. query the content of the connection server through SQL statements
Select Q .*
From openquery (DCs, -- connection server name
'Select filename, size, docauthor, path -- SQL statement in the connection Server
From scope ()
Where contains (''' and abc '')'
) As Q
2. Because SQL statements do not support dynamic input strings, the query string must be in the C # Code Splicing
S. P.
You can perform full-text search in the database without saving the file outside the database. The specific solution is to use the full-text security search function in SQL Server 2000 to store the file content with the image field and add a new field to store the file type, then, match the two fields when you create a full-text index in the Enterprise Manager. However, from the perspective of the database/rate and the efficiency of database backup and recovery in the future, the design of storing files in the database is not very good.