If you want to query Index Server through SQL Server, you must use the OPENQUERY function. The syntax structure is as follows.
OPENQUERY (pai_server, 'query ')
The linked_server parameter indicates the connection name. The query parameter is the query to be performed. It is transmitted to the OPENQUERY function in the form of a string. This function returns a virtual table, which allows us to perform further queries.
The following statement searches for all files whose content contains the word "SQL:
SELECT *
FROM OpenQuery (FileSystem,
'Select Directory, FileName, DocAuthor, Size, Create
From scope ()
Where contains (Contents, ''SQL '')')
For an Index Server query, the syntax format of the FROM statement is different FROM that of a common SQL statement. The syntax structure is as follows:
FROM [Catalog_Name ..] {SCOPE (['scope _ Arguments '])}
The Catalog_Name parameter specifies the index directory to be queried. Since we can only select one index directory when defining the connection, this parameter will be omitted here. The SCOPE function is used to specify the directory of the file to be queried. Shows the syntax structure of the SCOPE function.
The deep traversal of keyword indicates that all files in the directory will be queried, including all files in its subdirectories. The shallow traversal of keyword indicates that only files in the top-level directory are queried, not those in the subdirectories. If the depth OF the queried directory is not specified, the default value is deep traversal.
Physical_path and virtual_directory are physical directories and virtual directories respectively. Their meanings are clearly marked in the figure, so we will not explain them further here.
Index Server supports 50 file attributes, which can be used as common file attributes for query conditions or returned results:
Attribute name data type comment can be used in the order by clause or in the SELECT statement
The last Access time of the Access datetime file. Yes
Description or abstract of the Characterization nvarchar or ntext document, which is used by the Index Server. No
The time when the Create datetime file was created. Yes
The physical path of the Directory Nvarchar file, excluding the file name. Yes
DocAppName: Specifies the name of the application for which the file is created. For example, Microsoft Word 9.0. Yes
The author of the DocAuthor nvarchar document. Yes
DocComments nvarchar comments about the document. Yes
The company name of the DocCompany nvarchar document. Yes
DocLastAuthor nvarchar: the user who recently edited the document. Yes
The last printing time of the DocLastPrinted datetime document. Optional
The number of pages in the DocPageCount integer document. Optional
The number of images in the DocParaCount integer document. Optional
The current version of the DocRevNumber integer document. Yes
The topic of the DocSubject nvarchar document. Yes
DocTemplate: the template of the nvarchar document. Yes
DocTitle: the title of the nvarchar document. Yes
The number of words in the DocWordCount integer document. Optional
The unique identifier of the FileIndex Decimal (19,0) file. Yes
FileName nvarchar file name. Yes
Number of hits in the HitCount integer file (word matching query ). That is, the number of words in the query condition in the file. Yes
Path: the physical Path of the nvarchar file, including the file name. Yes
Rank the Rank of an integer row, ranging from 0 to 1000. The larger the number, the more matched. Yes
ShortFileName nvarchar short file name (in 8.3 format ). Yes
Size Decimal () file Size, in bytes. Yes
VPath nvarchar points to the complete virtual path of the file, including the file name. If there are multiple possible paths, select the one that best matches the query. Yes
Write datetime: the time when the last file was written. Yes
The preceding attributes can be used as the query conditions in the Where clause. When a condition CONTAINS a full-text search condition, the CONTAINS and FREETEXT statements must be used, which is the same as the full-text search of SQL Server. Note that the group by and HAVING clauses cannot be used in Index Server queries. This is not supported by Index Server.
In addition, you cannot use * to SELECT all attributes in the SELECT clause of the query. * Views can only be used for query. A view can be understood as a virtual table exported from a query. By querying the view, you can re-query the query results. Views are generally used when other queries are often used as data sources in the FROM clause. Use the create view statement to CREATE a VIEW. The syntax structure is as follows:
Create view view_name [(column] [, n])]
AS
Select_statement
View_name indicates the name of the view to be created, while the AS clause is followed by the query statement for creating the view.
This statement has the following restrictions: it cannot contain clauses such as order by, COMPUTE, and compute by; it cannot contain the INTO keyword; it cannot contain temporary tables.
Column is used to name fields in the view. Generally, these fields must be named only when they are expressions, functions, and constants.
In SQL Server, you can also use the CREATE VIEW statement to CREATE a VIEW.
The following is an example of the entire process of querying the Index Server.
In the virtual directories Corpus and C: emp, we will find the files whose authors have records in the writer table and whose words exceed 5000 words. Return the author's name, document question, and identity. Shows the query statement and specific operation process.
Index Server Query Process
The steps are described as follows:
1: The query is submitted to SQL Server, and the distributed query part (included in the OPENQUERY function) is passed to the SQL Server Distributed Query processor.
2: The Distributed Query processor Further transmits the query to ole db Provider for Indexing Service (MSIDXS), and MSIDX connects to the file system.
3: MSIDXS analyzes the query statement and sends the corresponding command to the file index service.
4: The file index service searches for qualified files from a virtual table that combines the Web virtual directory/Corpus and C: Temp and returns them as a row set to MSIDX.
5: MSIDXS returns the row set to the distributed query processor.
6: The Distributed Query processor queries the returned row set as a table in combination with the writers table and returns the final result to the query submitter.
IN the query of Index Server, predicates such as QUANTIFIED, COMPARISON, BETWEEN, EXISTS, IN, and NULL cannot be used. These are not supported by Index Server. However, it supports two predicates not directly supported by two SQL servers: MATCHES and ARRAY. We can use them in the query statement of Index Server. Its meaning and syntax structure are as follows:
MATCHES: used for pattern matching, which is similar to Like but more powerful. Its syntax structure is as follows: MATCHES (Column_Reference, '{Grouped_Search_Pattern Counted_Search_Pattern}')> 0
Column_Reference is the specified file attribute, and its data type must match Grouped_Search_Pattern.
Grouped_Search_Pattern indicates the specified matching mode. Counted_Search_Pattern is used to limit the number of matches. There are three restrictions:
Strict match: contains a specified number of matching strings in the queried file attributes.
At least match: The queried file attributes contain strings that meet the matching conditions greater than or equal to a specified number.
Range match: The queried file attributes contain the number of strings that meet the matching conditions. The value range is n ~ M.
The following table lists the modes that MATCHES can use:
Symbol description
* Contains the specified matching string and other 0 or more characters. This is very similar to * Used in the dir command. For example, the matching condition for a string starting with B and ending with d is B * d.
? It contains the specified matching string and other 0 or one character. If B? The strings matching the condition d include bd, bad, and bed.
+ Contains the specified matching string and one or more other characters. For example, strings that meet the B + d matching conditions include bad, bed, and bond.
() Is used to define pattern matching, indicating that the character conditions of pattern matching are in it. It is used when there are more than one condition.
{} Is used to define the matching quantity, indicating that the matching quantity condition is pattern matching quantity.
[] Specifies the range of a string in the mode condition.
An escape character, which must be used before each symbol above. To distinguish it from common characters.
The following describes how to use the MATCHES predicates through a few simple examples.
Search for files whose content contains "SQL" three times:
Where matches (DocText, '(SQL) {3}')> 0
Search for files whose content contains "SQL" more than three times:
Where matches (DocText, '(SQL) {3,}')> 0
Find the file that contains "SQL" three to ten times:
Where matches (DocText, '(Bora) {3, 10}')> 0
When you are not sure whether the author's name contains "Pellow" or "Pelow", you can use the following condition statement:
Where matches (DocAuthor, '* Pel {1, 2} ow')> 0
ARRAY: used to compare two arrays (also called vectors) by logical operators. The syntax structure is as follows:
Column_Reference Comparison_Operator [all some] ARRAY [Array_Elements]
Column_Reference is used to compare the file attributes. It can be multiple attributes. However, the data type must match Array_Elements.
Comparison_Operator is a comparison operator, which can be = ,! = (Not equal to),>, =>, <, <=.
Array_Elements indicates the arrangement used for comparison. It must be included in. Empty sorting is also allowed. For example, the following query is also valid.
SELECT foo from scope () WHERE bar = ARRAY []
ALL and SOME indicate different comparison methods for Array_Elements. For All, each element in the left-side arrangement is compared with the corresponding element in the right-side arrangement. For example, if the following expression is true for ALL, the result of the expression is false:
[1, 2, 3]> all array [1, 2]
For SOME, you only need to have a value in the left-side arrangement that is compared with all values in the right-side arrangement. Therefore, the following expression returns true for SOME:
[1, 2, 3]> some array [2, 1]
Because 3 in the left-side arrangement is larger than 2 and 1 in the right-side arrangement, the return value is true.
If not
