SQL Server Full-text search query for file contents

Because the project needs, you need to query the content of uploaded files. MSDN understands that the Windows Indexing Service enables Full-text retrieval of files and queries through SQL Server. The project combines the two to achieve a full text search solution for uploaded files.

Programme summary:

1. Change file name when files are stored

2. Configure the indexing server and associate the Index server with MS SQL Server.

3. Modify the SQL statement to include the contents of the Full-text query statement in the query criteria

How files are stored:

For convenience of storage and easy indexing, we store uploaded files in a directory, to ensure that the uploaded filename is not duplicated, using the GUID as the filename, and the GUID is associated with the database record. Also, the suffix of the file retains the suffix of the original file, allowing the Indexing Service to recognize the document.

Configure Indexing Service

Enter the Computer Management (Computer Management) program (right-click "My Computer"-> "manage") and find Indexing Service (Index services)

Configuring the Indexing Service functionality

(1) Right-click to select Indexing Service to pop up a menu

(2) Select New->catalog (Create a new catalog)

Set up a newly added catalog

(1) Enter the name of the catalog (the name needs to be associated with the database, be aware of the naming, where the assumption Dcsii

(2) Point browse Access Select Indexing Service run Directory form

(3) Select the index run directory that you created earlier (anywhere)

(4) Click OK to complete this operation

You need to add a catalog to a catalog after you create a record

(1) Right-click to select the catalog you just created

(2) Select New->directory (new directory)

New Index Directory

(1) Click Browse to enter the directory selection page

(2) Select the directory where you want to search for full-text files

(3) Determine the completion of this operation

Start Indexing Service When creation is complete

(1) Start Indexing Service

(2) When Indexing Service starts successfully indexing the catalog, you can see that the catalog information indicates that the service has started successfully.

To associate Indexing Service and SQL database:

Execute the following stored procedure in SQL Query Analyzer (SQL Analyer)

EXEC sp_addlinkedserver Dcs,--Connect the name of the server to the back of the query to use

' Index Server ',

' Msidxs ',

' Dcsii '--New catalog name in Indexing Service

S.P.

Indexing Service Performance Tuning Method 1

(1) Right-click to select Indexing Service

(2) Select all tasks-> tune Perrformance

(3) Select User Custom

(4) Click Customize to enter the performance adjustment form

(5) Move index and querying properties to the far right

(6) The point determines the completion of the operation

Performance Tuning Method 2

If the server does not need to index the entire system's files, you can either stop or remove system catalogs.

MS SQL calls to the Index server

1. Querying the contents of a connection server through SQL statements

　　SELECT Q.*
　　FROM OpenQuery(dcs, --连接服务器名字
　　'SELECT FileName, Size, DocAuthor, path --在连接服务器里的sql语句
　　FROM SCOPE()
　　WHERE CONTAINS(''番号 and abc'') '
　　) AS Q

2. Because the SQL statement does not support dynamically entered strings, the query string has to be spliced in C # code

S.P.

It is also possible to implement Full-text search in a database without saving files to a database. The specific scenario is secure Full-text search in SQL Server 2000, store the contents of the file with the Image field, add a new field to the file type, and then match the two fields when you create a new Full-text index entry through Enterprise Manager. However, from the database/rate and the efficiency of database backup and recovery in the future, it is not very good to put files in the database design.