SELECT Statement

  • Article

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 SELECT statement retrieves rows by specifying the columns of interest, the scope (the set of files) for the search, and the search criteria. If the results must be ordered, you can add an additional ORDER BY clause to return the rows in ascending or descending order.

<b>SELECT</b> Select_List | *
       FROM_Clause
       [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. A column alias can be either a well known Indexing Service friendly name or a new friendly name (regular_identifier) as defined by a SET PROPERTYNAME statement.

Note

Do not use "true" SQL expressions, such as: "SIZE + 500", and LENGTH(DocAuthor), in the Select_List parameter. These expressions are not supported.

 

* (asterisk)

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

FROM_Clause

Specifies the files on which to perform the search. For details about this parameter, see FROM Clause.

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

If the Select_List parameter specifies a column more than once, only one instance of that column appears in the result set.

SQL-92 specifies both the ALL and DISTINCT set quantifiers. Because Indexing Service retrieves all rows including duplicate rows, the ALL set quantifier is assumed and does not have to be specified. DISTINCT is not supported.

Examples

The following query returns the author, size, title, and the last time the file was modified for all files in the physical root directory that contain the phrase "Indexing Service".

SELECT DocAuthor, size, DocTitle, write
  FROM SCOPE()
  WHERE CONTAINS(Contents, '"Indexing Service"')

Suppose #MyDocView has been defined with a CREATE VIEW statement containing the properties DocAuthor, FileName, size, and access. Use of an asterisk selects all the properties in that view. The following statement returns DocAuthor, FileName, size, and access values for all rows where the file size is greater than 100000.

SELECT * FROM #MyDocView
  WHERE size > 100000

CREATE VIEW Statement

FROM Clause

ORDER BY Clause

WHERE Clause