CREATE VIEW Statement
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 CREATE VIEW statement defines and stores (for the duration of a session with Indexing Service) a set of frequently used properties. The name associated with this set of properties can be used with subsequent SELECT statements.
CREATE VIEW #View_Name AS SELECT Select_List FROM_Clause
Specifies the name for the view. The view name must be preceded by a number sign (#) to indicate that it is a temporary definition.
Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query.
Specifies the files on which to perform the search. For details about this parameter, see FROM Clause.
You can create a temporary view or use one of several predefined views supplied with the SQL extensions. Predefined views include the following.
Creating a view allows you to use SELECT * queries, which normally cannot be performed in Indexing Service. Without a view, you would have to specify the full list of columns each time you want to run such a query.
Note It is possible to issue a SELECT statement against a view where the Select_List is a subset of the columns in the view definition.
After you have associated a name with a view definition by means of the CREATE VIEW statement, you cannot associate it with another view definition. For example, if you execute the following two CREATE VIEW statements in succession, the second CREATE VIEW statement is an attempt to redefine the view, and it will fail.
CREATE VIEW #MyView1 AS SELECT DocAuthor FROM SCOPE() CREATE VIEW #MyView1 AS SELECT FileName FROM SCOPE()
The view name, along with its view definition, is implicitly associated with the catalog active at the time you define the view. If you switch catalogs, that view will not be found and you must redefine it. Consider the following pseudocode flow.
<default catalog is cat1>
CREATE VIEW #MyView1 AS SELECT DocAuthor, size FROM SCOPE()
<set current catalog to cat2>
The preceding SELECT statement will result in an error since #MyView1 has not been defined for cat2.
<set current catalog back to cat1>
The preceding SELECT statement will return hits if there are files within the specified scope.
Because there is no support for a DROP VIEW statement, once a view is created, it persists for the duration of the Indexing Service session.
Suppose you often query using the following properties and scope:
- DocAuthor, FileName, size, and last file access date.
- Query a scope of /specs/database/integration (deep traversal) and /current/specs (shallow traversal).
The following CREATE VIEW statement stores that information in a view and eliminates the need to reenter it for SELECT * queries.
CREATE VIEW #MySpecView AS SELECT DocAuthor, FileName, size, access FROM SCOPE(' DEEP TRAVERSAL OF "/specs/database/integration" ', SHALLOW TRAVERSAL OF "/current/specs" ')
As a result, all future queries can be expressed in a simpler manner. For example, you can issue the following queries:
SELECT * FROM #MySpecView WHERE size > 100000 SELECT * FROM #MySpecView WHERE CONTAINS (' "index search" ') SELECT FileName, size FROM #MySpecView WHERE FREETEXT ('how do I search with Indexing Service') AND FileName LIKE 'm%.doc'
Build date: 9/10/2012