Overview of Windows Search SQL Syntax
The Windows Search Structured Query Language (SQL) is similar to a standard SQL query. It is shown in the following two syntaxes:
SELECT [TOP <positive integer>] <columns> FROM [machinename.]SystemIndex [WHERE <conditions>] [ORDER BY <column>]
GROUP ON <column> [<ranges>] [AGGREGATE <aggregate_list>] [ORDER BY <column> [ASC/DESC]] OVER (<GROUP ON ...> | <SELECT...>)
In the following query example, the page count and date created values are returned for all documents which have more than 50 pages, sorted is ascending order of page count.
SELECT System.Document.PageCount, System.DateCreated FROM SystemIndex WHERE (System.Document.PageCount > 50) ORDER BY System.Document.PageCount
The Windows Search query syntax supports many options, enabling more complicated queries.
The following table describes each clause in the SELECT or GROUP ON statements and the features supported.
|GROUP ON...OVER...||Specifies how to group results returned by the query. You can specify the ranges by which to group and specify more than one column for grouping. For example, you can group results over a range of file sizes (size < 100, 100 <= size < 1000; 1000 <= size) and nest groupings.|
|SELECT||Specifies the columns returned by the query.|
|FROM||Specifies the machine and catalog to search.|
|WHERE||Specifies what constitutes a matching document. This clause has many options, enabling rich control over the search conditions. For example, you can match against words, phrases, inflectional word forms, strings, numeric and bitwise values, and multi-valued arrays. You can also apply statistical weights to the matching conditions, and combine matching conditions with Boolean operators.|
|ORDER BY||Specifies the sort order for the results returned by the query. You can specify more than one field on which the results are sorted, and you can use ascending or descending ordering.|
The WSSQL code sample demonstrates how to communicate between Microsoft OLE DB and Windows Search through SQL. The WSOleDB code sample illustrates Active Template Library (ATL) OLE DB access to Windows Search applications, and two additional methods for retrieving results from Windows Search. Both samples are available on Code Gallery and the Windows 7 SDK.
- Using Localized Searches
- Understanding Relevance Values
- Property Mappings
- Advanced Query Syntax
- SQL Extensions in Microsoft Windows Search
- SQL Features Unavailable in Microsoft Windows Search
- Case Sensitivity in Searches
- Diacritic Sensitivity in Searches
- Casting the Data Type of a Column
- Data Type Mappings