Export (0) Print
Expand All

FROM Clause

Note  Indexing Service is no longer supported as of Windows XP and is unavailable for use as of Windows 8. Instead, use Windows Search for client side search and Microsoft Search Server Express for server side search.

The FROM clause defines the query scope by specifying the files on which to perform the search. The FROM clause is a required part of the SELECT statement.

SELECT Select_List | *
       FROM {[Server_Name.][Catalog_Name..]Predefined_View_Name |
             [Server_Name.][Catalog_Name..]View_Name |
             [Server_Name.][Catalog_Name..]SCOPE('Scope_Arguments']) |
             (TABLE [Server_Name.][Catalog_Name..]SCOPE(['Scope_Arguments'])
             UNION ALL TABLE [Server_Name.][Catalog_Name..]SCOPE(['Scope_Arguments'])
             [UNION ALL TABLE [Server_Name.][Catalog_Name..]SCOPE(['Scope_Arguments']) 
             ...])}
       [WHERE_Clause]
       [ORDER_BY_Clause]

Parameters

Select_List

Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query.

* (asterisk)

Specifies all columns. This option is valid only when the FROM_Clause parameter references a predefined view or a temporary view.

Server_Name

Specifies the name of the server on which the query will be run. A Server_Name parameter can't be specified for a query that uses a temporary view because a temporary view is specified only for the current catalog. A Server_Name parameter can be specified for a query that uses a predefined view.

Catalog_Name

Specifies the catalog to use. A catalog is the highest-level unit of organization in Indexing Service. Each catalog is a completely self-contained unit containing an index and cached properties for one or more scopes (virtual or physical directories). If a query does not specify the Catalog_Name parameter, the default catalog "system" is used.

Scope_Arguments

The SCOPE function, which is the main component of the FROM clause, can take zero or more comma-separated Scope_Arguments parameters.

If Scope_Arguments is an empty list (for example, FROM SCOPE()), this specifies that the physical root is to be used as the default path.

If not empty, Scope_Arguments specifies the paths or virtual roots to be searched and how they will be traversed. Each Scope_Argument must be enclosed in single quotes. The syntax of each Scope_Argument is:


'[<i>Traversal_Type</i>] ("<i>Path</i>"[, "<i>Path</i>", ...])'

Traversal_Type can be either of these options:

DEEP TRAVERSAL OF

Searches the paths specified plus all directories beneath them.

SHALLOW TRAVERSAL OF

Searches the specified paths only.

You can group a set of directories or virtual roots for deep or shallow traversal. See the following Examples section.

If you do not specify Traversal_Type, the default option is DEEP TRAVERSAL OF.

Path can be specified either as physical directories, such as "d:\reports\year\97", or as virtual roots, like "/annual/corporate". A scope definition can include multiple physical paths and multiple virtual roots. Enclose each Path parameter with double quotes (to accommodate spaces in paths). If you specify more than one path, they must be separated by commas.

Predefined_View_Name

Specifies one of a set of Indexing Service predefined views or Site Server predefined views of often-queried properties.

View_Name

Specifies a nonpersistent view defined with the CREATE VIEW statement.

Although the SCOPE function restricts the lifetime of the virtual-table definition to the current query, you can use a CREATE VIEW statement to define combinations of properties and scope for use in subsequent queries. For more information, see the CREATE VIEW Statement.

WHERE_Clause

Specifies the search condition for selecting rows in the virtual table defined by the FROM_Clause parameter. The matching rows make up the resulting rowset. This clause is optional. For details about this parameter, see WHERE Clause.

ORDER_BY_Clause

Specifies the ordering of the resulting rowset. This clause is optional. For details about this parameter, see ORDER BY Clause.

Remarks

When using one or more periods (.) to separate the Server_Name, Catalog_Name, SCOPE(), Predefined_View_Name, and View_Name parameters, do not include any spaces.

If some, but not all, of the paths you specify do not exist, these invalid paths are ignored and the query is made using only the valid paths. If all the paths you specify are invalid, then the result set is always empty.

Examples

The following example defines a deep traversal search (default scope). It searches for the word smith in the DocAuthor property for all files, starting from the physical root directories in the catalog.


SELECT DocAuthor, DocTitle
  FROM SCOPE()
  WHERE CONTAINS(DocAuthor, 'smith')

The following example defines a shallow traversal of \Contracts\open on drive D (the top directory only) and all the subdirectories at /reports/year 97 from a specific catalog called Contracts.


SELECT DocAuthor, size,  DocTitle,  write
  FROM Contracts..SCOPE(' SHALLOW TRAVERSAL OF "D:\Contracts\open" ',
             ' DEEP TRAVERSAL OF "/Reports/Year 97" ')
  WHERE CONTAINS(DocTitle, '"Contract Expenses"')

Because DEEP TRAVERSAL OF is the default traversal selection, its inclusion is optional. You may want to use it to increase readability of the statement. Notice that single quoted strings are used to associate traversal type to a specific path or set of paths.

The following example statements are equivalent, but the FROM clause in the latter is more verbose than in the former. In the second example, extra parentheses are added around the paths to enhance readability. Also, the examples demonstrate the syntax for querying a remote server.


SELECT DocAuthor, size,  DocTitle,  write
  FROM SpecServer.Specs..SCOPE(' "D:\Word97\specs", "E:\DAPI\specs"  ' )
  WHERE CONTAINS(Contents, '"content search"')
SELECT DocAuthor, size,  DocTitle,  write
  FROM SpecServer.Specs..SCOPE(' DEEP TRAVERSAL OF ( "D:\Word97\specs",    "E:\DAPI\specs" ) ')
  WHERE CONTAINS(Contents, '"content search"')

The following example demonstrates a query against multiple catalogs. It returns results for matches that contain the phrase "content search" from the Specs catalog and the default catalog.


SELECT DocAuthor, size,  DocTitle,  write
  FROM (TABLE Specs..SCOPE()
        UNION ALL TABLE SCOPE())
  WHERE CONTAINS(Contents, '"content search"')

The following example specifies a Server_Name but not a Catalog_Name, which defaults to "System".


SELECT DocAuthor, FileName
  FROM MyServer...SCOPE()

The preceding example is therefore equivalent to the following example.


SELECT DocAuthor, FileName
  FROM MyServer.System..SCOPE()

If the Server_Name or Catalog_Name parameter contains any non-alphanumeric characters, the name must be double-quoted as in the following example.


SELECT DocAuthor, FileName
  FROM "My-Server".System..SCOPE()

Related topics

ORDER BY Clause
SELECT Statement
WHERE Clause

 

 

Build date: 9/10/2012

Community Additions

ADD
Show:
© 2014 Microsoft