CREATE VIEW Statement

The CREATE VIEW statement defines and stores (for the duration of the session with Index Server) a set of properties that you frequently use. You can create a temporary view or use one of several predefined views supplied with the SQL extensions. Creating a VIEW allows you to use SELECT * queries, which normally cannot be carried out in Index Server. 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 against a view where the Select_List is a subset of the columns in the view definition

Syntax

The simple query specification consists of a SELECT list and a FROM clause.

CREATE VIEW #View_Name  AS SELECT  Select_List FROM_Clause

Elements

Once you have a name associated with a view definition using the CREATE VIEW statement, you cannot associated it with another view definition. For example, if you execute the following two CREATE VIEW statements in succession:

CREATE VIEW #MyView1 AS SELECT DocAuthor FROM SCOPE()

CREATE VIEW #MyView1 AS SELECT FileName FROM SCOPE()

The second CREATE VIEW is an attempt at redefinition and will fail.

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. So in the following pseudo code flow:

<default catalog is cat1>

CREATE VIEW #MyView1 AS SELECT DocAuthor, size FROM SCOPE()

<set current catalog to cat2>

SELECT * FROM #MyView1 will result in an error since #MyView1 has not been defined to cat2.

<set current catalog back to cat1>

SELECT * FROM #MyView1 will return hits if there are files within SCOPE().

Example

Suppose you often query for the following:

• DocAuthor, FileName, size, and last file access date.
• Query SCOPE of /specs/database/integration (deep traversal) and /current/specs (shallow traversal).

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:

SELECT * FROM #MySpecView WHERE size > 100000

SELECT * FROM #MySpecView 
   WHERE CONTAINS (' "index search" ') > 0
   
Select FileName, size FROM #MySpecView
   WHERE FREETEXT ('how do I search with Index Server') > 0
   AND FileName LIKE 'm%.doc'

Predefined Views

The SQL extensions include several predefined views that you can use without writing an explicit CREATE VIEW statement. Note that because the predefined view names are not temporary, they do not need to be preceded by the pound (#) sign.

List of Predefined Views

These predefined views are always available, independent of which catalog you are currently using.

Example

The following example shows the conceptual SQL statement to the supplied FILEINFO view. Note that the predefined views are constructed using the default scope.

CREATE VIEW FILEINFO AS
SELECT path, FileName, size, write, attrib
FROM SCOPE()

After creating the view of the previous example, you can issue queries such as:

SELECT * FROM FILEINFO
WHERE size > 20000

SELECT path, FileName, attrib
FROM FILEINFO
WHERE attrib = ALL ARRAY [0x820]
OR FileName LIKE 'arch%.xls'

© 1997 by Microsoft Corporation. All rights reserved.