Overview of the SELECT Statement

  • Article

The SharePoint Portal Server search query statement is much like a standard SQL query, as shown in the following syntax:

SELECT <columns>
FROM <catalogs>
WHERE <conditions> 
RANK BY <conditions>
ORDER BY <columns>

In the following query example, the document size, title, and relevance ranking are returned for documents stored in and under the directory file://server/documents that are larger than 10,000 bytes long. Each matching file is assigned the rank value of 1000, and the results are sorted in ascending size order.

SELECT 
"DAV:getcontentlength",
"urn:schema-microsoft-com:office:office#Title",
"urn:schemas-microsoft-com:fulltextqueryinfo:rank"
FROM SCOPE ('DEEP TRAVERSAL OF "file://server/documents"')
WHERE ("DAV:getcontentlength" > 10000)RANK BY COERCION ( Absolute, 1000 )ORDER BY "DAV:getcontentlength"

The SharePoint Portal Server SQL query syntax supports many options, enabling more complicated queries. The following table shows each clause and a brief description of the features it supports.

Clause Description
SELECT Specifies the columns returned by the query. You can create aliases for long column names, and use URNs as column names.
FROM Specifies the catalogs and folders in which the results must be located. You can request that SharePoint Portal Server search for documents in the specified folder, or that SharePoint Portal Server search for matching documents in the folder and in all subfolders. By using the COALESCE_TABLE function, you can request that the query return the results of the first matching search from more than one 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 multivalued arrays. You can also apply statistical weights to the matching conditions, and combine matching conditions by using Boolean operators.
RANK BY Specifies modifications to the normal methods for calculating rank. Allows you to specify weights for specific matching conditions, and to multiply or add values to the normal calculated rank results.
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.