Overview of Windows Search SQL Syntax

Article
01/07/2021

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.

Clause	Description
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.

Code samples

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 GitHub.